0

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?

parboy
  • 67
  • 8

2 Answers2

0

You can LEFT JOIN table_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
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Tim - I tried this and got an error on line 3. I checked typos and syntax and didn't find an error. – parboy Jan 08 '16 at 01:38
  • Your original syntax was off. Try using `INSERT INTO...SELECT...FROM`. – Tim Biegeleisen Jan 08 '16 at 01:40
  • Tim - you were correct, I missed the change from VALUES to SELECT. Having corrected that, I get an error that col1 in the SELECT statement is ambiguous. I don't see why since it's specified as FROM table_a. If it's ambiguous because of the JOIN, how do I add a table reference in the SELECT line? – parboy Jan 08 '16 at 01:52
  • `col1` was ambiguous because apparently both `table_a` and `table_b` have such a column. So MySQL could not figure out to which column you were referring. I have corrected this by explicitly referring to `table_a`. – Tim Biegeleisen Jan 08 '16 at 01:54
  • I've added the table reference to the select fields and got rid of the ambiguity. The result now errs: Operand should contain 1 column(s). – parboy Jan 08 '16 at 04:08
  • Remove the parentheses from your `SELECT` clause, q.v. [this SO post](http://stackoverflow.com/questions/456644/mysql-syntax-error-message-operand-should-contain-1-columns). – Tim Biegeleisen Jan 08 '16 at 04:15
  • Thanks Tim - appreciate you staying with this. – parboy Jan 08 '16 at 16:46
0
SELECT primaryField
From Table_a
WHERE primaryField NOT IN (SELECT primaryField FROM Table_b)

and you query can be 

INSERT INTO table_b (`col1`,`col2`,`col3`,etc...)
VALUES (`col1`,`col2`,`col3`,etc...)
FROM table_a
WHERE table_a.primaryField NOT IN (SELECT primaryField FROM table_b.primaryField)
Abbas Palash
  • 560
  • 1
  • 6
  • 7
  • Abbas - I had tried the 2nd part as a query and it didn't work. I wasn't sure what to do with the first part. Was the 2nd part supposed to be a subquery? – parboy Jan 08 '16 at 01:41