CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Steven
Steven

Reputation: 18869

Update multiple rows using select statement

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?

Upvotes: 21

Views: 120350

Answers (5)

Enayat
Enayat

Reputation: 4042

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
    ...

Upvotes: 3

Francisco R.
Francisco R.

Reputation: 11

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...

Upvotes: 1

rageit
rageit

Reputation: 3611

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270583

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.

Upvotes: 10

user1166147
user1166147

Reputation: 1610

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.

Upvotes: 40

Related Questions