Copy unique data from one table to another

问题:

I have two tables. Table A is the main table, and table B an auxiliary table. Now i need to copy from table B to table A only the rows with a aux_id not present in table A

table_A

id |aux_id | firstname | lastname

table_B

 aux_id | firstname | lastname

What I am trying, but apparently without success.

INSERT INTO table_A(aux_id,firstname,lastname)(SELECT aux_id,firstname,lastname FROM table_B WHERE aux_id != aux_id);

I am getting : INSERT 0 0

This is wrong, because i am sure that I have new entries in table B.

回答1:

There are many ways to do this (left join, not exists, not in). If you are learning SQL, NOT IN, might be the easiest on first encounter:

INSERT INTO table_A(aux_id, firstname, lastname)
    SELECT aux_id, firstname, lastname
    FROM table_B
    WHERE b.aux_id NOT IN (SELECT a.aux_id FROM table_A a);

I should say that I much prefer NOT EXISTS:

    WHERE NOT EXISTS (SELECT 1 FROM table_A a WHERE a.aux_id = b.aux_id);

The reason has to do with the handling of NULL values. The NOT EXISTS does what you expect. NOT IN never returns true if the subquery returns a NULL value.