#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    99
    Rep Power
    8

    Query to compare two tables and add record


    Hi, I'm trying to do a query that compares two tables, VOLUNTEER and APPLICANT and check to see if a record in APPLICANT is existent in VOLUNTEER - and if it's not, then add it to VOLUNTEER. But so far I've been only able to come up with this (it actually adds a record regardless if the record is in VOLUNTEER or not...which is not really what I'm looking for...

    Code:
    INSERT INTO VOLUNTEER(volunteer_id, last_name, first_name) SELECT volunteer_id, last_name, first_name FROM APPLICANT
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Location
    BRAZIL
    Posts
    25
    Rep Power
    0
    you're pretty close to the solution... you just forgot the WHERE clause
    remember you need to identify the tables primary keys and then rewrite your query like this:


    INSERT INTO VOLUNTEER(volunteer_id, last_name, first_name)
    SELECT volunteer_id, last_name, first_name
    FROM APPLICANT A
    WHERE A.volunteer_id NOT IN
    (SELECT V.volunteer_id FROM VOLUNTEER V)

IMN logo majestic logo threadwatch logo seochat tools logo