SQL如何对不同表的数据进行更新

如果我们有表A和表B, 我想把我的表A的Col1内的数据更新到表B的Col1里面，那么我们怎么做呢？

Microsoft SQL例子

UPDATE        scores
SET           scores.name = p.name
FROM          scores s
INNER JOIN    people p
ON            s.personId = p.id

MySQL例子

UPDATE    scores s,
          people p
SET       scores.name = people.name
WHERE     s.personId = p.id

高阶使用

当我们从一个上传的表，与多个表合并查询后，再更新，就会花费很多时间。那么，有没有办法一次性，将上传的表与需要的数据合并后再根据条件更新呢？

MERGE SQL使用

The MERGE statement is used to manipulate (INSERT, UPDATE, DELETE) a target table by referencing a source table for the matched and unmatched rows. The MERGE statement can be very useful for synchronizing the table from any source table.

Now, if we go back to our position, the MERGE statement can be used as an alternative method for updating data in a table with those in another table. In this method, the reference table can be thought of as a source table and the target table will be the table to be updated. The following query can be an example of this usage method.

MERGE Persons AS Per 
USING(SELECT * FROM AddressList) AS Addr   
ON Addr.PersonID=Per.PersonID 
WHEN MATCHED THEN
UPDATE SET 
Per.PersonPostCode=Addr.PostCode ,
Per.PersonCityName = Addr.City;

参考文献

1. TravisHorn
2. SQLs Hack