How to copy an sql column based on foreign key?-CodePudding

How do I run the following query

INSERT INTO table1
SELECT sourceId
FROM table2
WHERE table2.id = table1.productId

given that table2 has an id column and a sourceId column (and few others) and table1 already contains productId and I want to copy the sourceId column into the table as well?

The error message I'm getting with this query is simply "Unknown column 'table1.productId' in where clause", but if I include table1.productId in the SELECT and table1 on the FROM row, I get the "Column count doesn't match value count at row 1" error.

Any idea how to fix up the query?

CodePudding user response：

when you are inserting into a table, you have to specify what to insert in all columns of that table. Should look like this:

INSERT INTO table1 (column1, column2, column3, ...)
SELECT value1, value2, value3, ...
FROM table2
WHERE ...;

But in your case since you said table1 already contains productId, you probably should do an UPDATE and not an INSERT

UPDATE table1 t1
SET t1.sourceId = 
(SELECT sourceId
FROM table2
WHERE table2.id = t1.productId);

If this works for you, you can improve on it with JOINs

CodePudding user response：

The select part is supposed to work stand-alone, so you must select from table1 in that part, if you want to refer to its rows.

INSERT INTO table1
SELECT sourceId
FROM table2
WHERE table2.id IN (SELECT productId FROM table1);