SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select large amount of ID's that does not exist on the other table?

    Hey,
    I have two tables and I need to add id-numbers from table B to table A, but only those id's that does not already exist in table A.

    I was thinking to first check every id from table A and make a comma separated list of it, and then SELECT from table B like: SELECT id FROM b where id NOT IN(large_list_of_ids). And after that insert those id's to table A.

    But this does not seem to be very efficient way. Are there any tips or tricks how this should be done?

    And please note that I could just keep an record of autoincrement number from table B, and always select and transfer those id's that came after last "autoincrement save point", but this is not fool proof enough. I have to check the whole B table and make sure all id's will get copied to table A that does not exist in table A.

    Any sugestions?

    And I forgot to mention, I'm using MYSQL.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    INSERT INTO a
     
    SELECT id 
    FROM b 
    WHERE id NOT IN (SELECT id FROM a)

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And oh heck, those tables A and B are on different servers. That INSERT INTO a SELECT.. wouldn't work in that case.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by js_11 View Post
    And I forgot to mention, I'm using MYSQL.
    1. okay, so please post in the mysql forum in future

    2. try INSERT IGNORE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I should have posted to the mysql forum, but at the time of writing, I was too confused thinking about how to explain my problem clear enough, slipped into a wrong area

    But thanks, I'll give it a try. Just plain simple select all id's from table B and then INSERT IGNORE to table A.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •