SQL如何基于公共匹配列将一张表的数据更新到另一张表

现有表结构与数据 #

• test1表包含字段Name、ID、Refnum，对应数据如下：

• test2表包含字段Username、Userid、Usernum，对应数据如下：

需求说明 #

当test1的Refnum与test2的Usernum值相等时，将test2对应行的Username、Userid分别更新为test1对应行的Name、ID值。

注意：执行批量更新操作前建议先备份表数据，也可以先运行关联SELECT查询验证匹配结果是否符合预期，避免误改数据

各数据库适配SQL语句 #

MySQL 版本 #

UPDATE test2 t2
INNER JOIN test1 t1 ON t2.Usernum = t1.Refnum
SET t2.Username = t1.Name,
    t2.Userid = t1.ID;

SQL Server 版本 #

UPDATE t2
SET t2.Username = t1.Name,
    t2.Userid = t1.ID
FROM test2 t2
INNER JOIN test1 t1 ON t2.Usernum = t1.Refnum;

Oracle 版本 #

UPDATE test2 t2
SET (t2.Username, t2.Userid) = (
    SELECT t1.Name, t1.ID
    FROM test1 t1
    WHERE t1.Refnum = t2.Usernum
)
WHERE EXISTS (
    SELECT 1
    FROM test1 t1
    WHERE t1.Refnum = t2.Usernum
);

PostgreSQL 版本 #

UPDATE test2 t2
SET Username = t1.Name,
    Userid = t1.ID
FROM test1 t1
WHERE t2.Usernum = t1.Refnum;

内容的提问来源于stack exchange，提问作者vinay