Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

mySql QUERY HELP

Options
  • 26-09-2004 8:01am
    #1
    ChicoMendez
    Registered Users Posts: 648 ✭✭✭


    What im trying to do is copy the rows of one table into another table where the row does not exist.

    INSERT INTO TableA SELECT * FROM TableB WHERE TableA.no <> TabelB.no

    Is there any way i can do this with one SQL statement?
    Ive tryed several options but i keep getting SQL errors thrown up!

    Tnx


Comments

  • Options
    #2
    MrNuked
    Closed Accounts Posts: 989 ✭✭✭MrNuked


    I think SELECT INTO should work.

    http://www.w3schools.com/sql/sql_select_into.asp

    W3C tutorial is extremely useful.


  • Options
    #3
    nahdoic
    Registered Users Posts: 944 ✭✭✭nahdoic


    do a primary key on the "no" column and it won't matter - because it will only allow for unique entries.


  • Options
    #4
    ChicoMendez
    Registered Users Posts: 648 ✭✭✭ChicoMendez


    nahdoic wrote:
    do a primary key on the "no" column and it won't matter - because it will only allow for unique entries.

    If i make a primary key on no...it does insert duplicates!

    If i make no a unique field it throws up a duplicate error exception hmmmmmmm


    :(

    any ideas? as it would be the perfect solution (if i could just supress the duplicate entry errors!)


  • Options
    #5
    jmcc
    Registered Users Posts: 7,412 ✭✭✭jmcc


    ChicoMendez wrote:
    What im trying to do is copy the rows of one table into another table where the row does not exist.

    INSERT INTO TableA SELECT * FROM TableB WHERE TableA.no <> TabelB.no

    Is there any way i can do this with one SQL statement?
    Ive tryed several options but i keep getting SQL errors thrown up!
    The TableA.no<>TableB.no may be a problem. Try using a LEFT JOIN to find the entries in TableB for which TableB.no does not exist in TableA.no. You also should just specify the columns in TableB that you want to add to TableB as that way you avoid trying to add keys etc. This is important because some keys may be set to auto_increment.

    If I rem correctly, the syntax is should be something like
    FROM TableB LEFT JOIN TableA ON TableA.no=TableB.no WHERE TableA.no IS NULL;

    It is a potentially dangerous and messy fix which may not work but MySQL is a toy piece of db software without things like UNIONs. And I think SUBSELECTs are only available in MySQL version 4.n. (I have to work on a 30G MySQL db later tonight and I am not looking forward to it. :) )

    Regards...jmcc


Advertisement