SQL如何基于两表关联列匹配值更新目标表的指定列数据

以下是针对两表按Part NO匹配、更新表1对应Description字段需求的不同主流数据库实现：

1. MySQL / PostgreSQL 适配写法 #

UPDATE table1 t1
INNER JOIN table2 t2 
  ON t1.`Part NO` = t2.`Part NO`
SET t1.Description = t2.Description;

2. SQL Server 适配写法 #

UPDATE t1
SET t1.Description = t2.Description
FROM table1 t1
INNER JOIN table2 t2
  ON t1.[Part NO] = t2.[Part NO];

3. Oracle 适配写法 #

UPDATE table1 t1
SET t1.Description = (
  SELECT t2.Description 
  FROM table2 t2 
  WHERE t1."Part NO" = t2."Part NO"
)
WHERE EXISTS (
  SELECT 1 
  FROM table2 t2 
  WHERE t1."Part NO" = t2."Part NO"
);

4. 通用标准SQL写法（兼容绝大多数数据库） #

UPDATE table1
SET Description = (
  SELECT t2.Description
  FROM table2 t2
  WHERE table1.`Part NO` = t2.`Part NO`
)
WHERE EXISTS (
  SELECT 1
  FROM table2 t2
  WHERE table1.`Part NO` = t2.`Part NO`
);

注意事项 #

• 执行更新操作前建议先运行以下查询验证匹配结果，避免误改数据：

SELECT 
  t1.`Part NO`,
  t1.Description AS 原描述,
  t2.Description AS 新描述
FROM table1 t1
INNER JOIN table2 t2 
  ON t1.`Part NO` = t2.`Part NO`;

• 字段名含空格时需要按数据库类型选择转义符：MySQL用反引号`，SQL Server用方括号[]，Oracle用双引号"。

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