Update multiple rows using select statement

Question

I have these tables and values:

Table1
------------------------
ID | Value
------------------------
2 | asdf
4 | fdsa
5 | aaaa


Table2
------------------------
ID | Value
------------------------
2 | bbbb
4 | bbbb
5 | bbbb

I want to update all the values in Table2 using the values in Table1 with their respective ID's.

Is there a way to do that with a simple SQL query?

possible duplicate of [SQL Server UPDATE from SELECT](http://stackoverflow.com/questions/2334712/sql-server-update-from-select) — D'Arcy Rittich, Jun 28 '12 at 15:32

score 39 · Accepted Answer · answered Jun 28 '12 at 15:39

39

Run a select to make sure it is what you want

SELECT t1.value AS NEWVALUEFROMTABLE1,t2.value AS OLDVALUETABLE2,*
FROM Table2 t2
INNER JOIN Table1 t1 on t1.ID = t2.ID

Update

UPDATE Table2
SET Value = t1.Value
FROM Table2 t2
INNER JOIN Table1 t1 on t1.ID = t2.ID

Also, consider using BEGIN TRAN so you can roll it back if needed, but make sure you COMMIT it when you are satisfied.

answered Jun 28 '12 at 15:39

user1166147

1,570
2
15
17

1

the above solution works like a charm, we can even add 'where' clause at the end on " '`join` & `on`" clause make sure to `rollback transaction tx1` – Manoj Kalluri Aug 05 '19 at 20:57

score 10 · Answer 2 · answered Jun 28 '12 at 15:39

If you have ids in both tables, the following works:

update table2
    set value = (select value from table1 where table1.id = table2.id)

Perhaps a better approach is a join:

update table2
    set value = table1.value
    from table1
    where table1.id = table2.id

Note that this syntax works in SQL Server but may be different in other databases.

score 3 · Answer 3 · answered Jul 18 '13 at 14:52

3

You can use alias to improve the query:

UPDATE t1
   SET t1.Value = t2.Value
  FROM table1 AS t1
         INNER JOIN 
       table2 AS t2
         ON t1.ID = t2.ID

answered Jul 18 '13 at 14:52

rageit

3,513
1
26
38

score 3 · Answer 4 · answered May 10 '18 at 13:36

3

None of above answers worked for me in MySQL, the following query worked though:

UPDATE
    Table1 t1
    JOIN
    Table2 t2 ON t1.ID=t2.ID 
SET
    t1.value =t2.value
WHERE
    ...

answered May 10 '18 at 13:36

Enayat

3,904
1
33
47

score 1 · Answer 5 · answered Aug 03 '15 at 22:27

I have used this one on MySQL, MS Access and SQL Server. The id fields are the fields on wich the tables coincide, not necesarily the primary index.

UPDATE DestTable INNER JOIN SourceTable ON DestTable.idField = SourceTable.idField SET DestTable.Field1 = SourceTable.Field1, DestTable.Field2 = SourceTable.Field2...

Update multiple rows using select statement

5 Answers5

Linked