SQL开发：基于关联条件从另一表更新列数据

针对你需要用table2的标准化城市格式更新table1的需求，我整理了不同数据库环境下的实现方案，完全贴合你的匹配条件和过滤要求：

核心逻辑拆解 #

• 关联条件：table1.dirty1 = table2.dirty2
• 额外筛选：仅更新table1.code < 0的记录
• 过滤无效数据：跳过table2中无有效标准化格式的记录（比如Dtroit、tchicgo这类异常值，或空值）

MySQL 实现代码 #

UPDATE table1
JOIN table2 ON table1.dirty1 = table2.dirty2
SET table1.standard_city = table2.standard_city
WHERE table1.code < 0
  -- 过滤空值和指定的无效标准化值
  AND table2.standard_city IS NOT NULL
  AND table2.standard_city NOT IN ('Dtroit', 'tchicgo');

PostgreSQL 实现代码 #

PostgreSQL用FROM子句来关联表，语法略有不同：

UPDATE table1
SET standard_city = table2.standard_city
FROM table2
WHERE table1.dirty1 = table2.dirty2
  AND table1.code < 0
  AND table2.standard_city IS NOT NULL
  AND table2.standard_city NOT IN ('Dtroit', 'tchicgo');

SQL Server 实现代码 #

SQL Server推荐用表别名简化语句：

UPDATE t1
SET t1.standard_city = t2.standard_city
FROM table1 t1
INNER JOIN table2 t2 ON t1.dirty1 = t2.dirty2
WHERE t1.code < 0
  AND t2.standard_city IS NOT NULL
  AND t2.standard_city NOT IN ('Dtroit', 'tchicgo');

注意事项 #

1. 如果table2中的无效标准化记录不是固定值（比如是空字符串、N/A这类标识），可以调整过滤条件，比如把NOT IN换成AND table2.standard_city != ''或者AND table2.standard_city NOT LIKE '%invalid%'
2. 执行更新前建议先跑一遍SELECT语句验证匹配结果，避免误更新：

   -- 以MySQL为例
   SELECT t1.*, t2.standard_city
   FROM table1 t1
   JOIN table2 t2 ON t1.dirty1 = t2.dirty2
   WHERE t1.code < 0
     AND t2.standard_city IS NOT NULL
     AND t2.standard_city NOT IN ('Dtroit', 'tchicgo');
   

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