Ad

Move Records With Unique Field Into 2nd Table

- 1 answer

I have 2 tables. table_a is current data, table_b has updated data. The 1st thing I need to do is move all new records, i.e., move the records from table_a to table_b that have a value in a primary index field (primaryField) not found in table_a.

I've tried variations of the following:

INSERT INTO table_b (`col1`,`col2`,`col3`,etc...)
VALUES (`col1`,`col2`,`col3`,etc...)
FROM table_a
WHERE table_a.primaryField NOT IN (SELECT table_b.primaryField)

This approach doesn't work. How do you select only the rows in a table that have values for a specific field not found in the matching field of a 2nd table?

Ad

Answer

You can LEFT JOINtable_a to table_b and then insert only those records in table_a which do not match anything in table_b.

INSERT INTO table_b (col1, col2, col3)
SELECT a.col1, a.col2, a.col3
FROM table_a a LEFT JOIN table_b b ON a.primaryField = b.primaryField
WHERE b.primaryField IS NULL
Ad
source: stackoverflow.com
Ad