CODEWITHSUNDEEP
mysql
geoffs3310
geoffs3310

Reputation: 14008

MySQL update with sub select

I have a table lets call it table1, and the data in two of the columns has been destroyed.

Fortunately I have an old backup of the table.

In the old backup the values for the two columns in question are correct but the rest of the columns are outdated so I can't simply import the whole dump.

So instead I've imported it into another table which I'll call table2. Each record has an id which is the same in both tables.

So basically I need a query that will go through each record in table1 and update column1 and column2 with the corresponding values from table2.

Upvotes: 27

Views: 60817

Answers (3)

niktrs
niktrs

Reputation: 10056

Original table is table1 and backup table is table2

UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.id
SET t1.col1 = t2.col1, t1.col2 = t2.col2, ...

Upvotes: 55

danyolgiax
danyolgiax

Reputation: 13116

I think you can try something like this:

UPDATE table1 SET table1field = (SELECT MAX(table2.table2field) FROM table2 WHERE table1.table1field = table2.table2field)

Upvotes: 0

Bohemian
Bohemian

Reputation: 425448

This will work on all flavours of SQL database:

update table1 t set
column1 = (select column1 from old_table where id = t.id),
column2 = (select column2 from old_table where id = t.id);

There's no need for any special/aggregate functions, because the id match will hit exactly one row.

Upvotes: 25

Related Questions