combining data from two tables with same primary key

Question

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?

Don't under any circumstances share that error message with us. — Strawberry, Jul 15 '20 at 21:01
Insert would create a new record. What you are looking for is update. But I'm curious why you would want to denormalise your data by moving location into the first table — Shadow, Jul 15 '20 at 21:01
Your `location` field in `table1` has no purpose in 3rd Normal Form. Get rid of it, then: `SELECT t1.name, t2.location FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id` — StackSlave, Jul 15 '20 at 21:51

forpas · Accepted Answer · 2020-07-15T21:35:08.353

0

Actually you want to update Table1:

UPDATE table1 t1
INNER JOIN table2 t2
ON t1.id = t2.id
SET t1.location = t2.location;

edited Jul 15 '20 at 21:35

answered Jul 15 '20 at 21:02

forpas

this still returns a syntax error – bguan2020 Jul 15 '20 at 21:32
What is the syntax error? – forpas Jul 15 '20 at 21:33
ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select t1.location = t2.location' at line 4. – bguan2020 Jul 15 '20 at 21:34
Yes I saw it. A typo, I wrote SELECT instead of SET. See my edited answer. – forpas Jul 15 '20 at 21:35
See a demo: https://www.db-fiddle.com/f/hRrJxMtehJuVmePPYPfQ96/0 – forpas Jul 15 '20 at 21:36
1

ah it works, thank you very much! – bguan2020 Jul 15 '20 at 21:36

1 Answers1