Skip to content
Advertisement

combining data from two tables with same primary key

I have a table like this:

table1
id   | name  | location
------------------------
1    | brian | null
2    | john  | null

and another table like this:

table2
id   | location
------------------------
2    |  USA
1    |  China

I want to INSERT the locations into the table1 ON t1.id=t2.id so table1 looks like this:

table1
id   | name  | location
------------------------
1    | brian | China
2    | john  | USA

For some reason though, when I run

INSERT INTO table1
SELECT location FROM table2
WHERE table1.id = table2.id;

I get a syntax error. Does anyone know how to insert it correctly?

Advertisement

Answer

Actually you want to update Table1:

UPDATE table1 t1
INNER JOIN table2 t2
ON t1.id = t2.id
SET t1.location = t2.location;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement