I have the following two tables that store the same data but have different IDs
Table 1:
| ID | name | address |
|---|---|---|
| 1 | John | Foo |
| 20 | Mary | Bar |
Table: 2
| ID | name | address | OLD_ID |
|---|---|---|---|
| 200 | John | Foo | |
| 23 | Mary | Bar |
I'd like to update table 2 by inserting IDs from table 1 into OLD_ID column name and address match.
| ID | name | address | OLD_ID |
|---|---|---|---|
| 200 | John | Foo | 1 |
| 23 | Mary | Bar | 20 |
I can do some horrible monstrosity that involves looping over each row in the second table, but I think there might be a way to do this using some CTE query magic.
CodePudding user response：
It's quite simple query. You just need update table2.old_id by setting it values from table1.id for rows where their name and address values are equal.
UPDATE table2 SET old_id = (
SELECT id FROM table1
WHERE table1.name = table2.name AND table1.address = table2.address
)