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?

回答1:

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.

回答2:

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.

回答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

回答4:

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

回答5:

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

回答6:

SET SQL_SAFE_UPDATES = 0;
UPDATE m1,m2 
 SET m1.code = m2.code
WHERE
  m1.name = (select distinct m2.name from  m2 limit 1);

Here, updation is taking palce in m1.code, where condtion is searching all records in sub query and returning all the matching rows.I use limit 1 to return one instead of duplicates. You can use distinct or limit 1 or top 1 or having count < 1 Sry, for my English