SQL Update子查询问题排查：如何正确更新mytable的codeIcaoAirport字段

你之前的CTE更新语句出错，核心原因是没有在UPDATE操作中建立目标表与CTE结果集的精准关联，导致所有符合codeIcaoAirport IS NULL的记录都被覆盖成了CTE结果集中最后一条的airports.ident值。

以下是几种基于m.codeIataAirport = a.iata_code关联条件的正确写法，适配不同SQL方言：

1. 标准SQL（PostgreSQL、SQL Server等适用） #

使用UPDATE...FROM语法直接关联两张表：

UPDATE mytable m
SET codeIcaoAirport = a.ident
FROM airports a
WHERE m.codeIcaoAirport IS NULL
  AND m.codeIataAirport = a.iata_code;

2. MySQL 多表更新写法 #

用JOIN关联后直接更新：

UPDATE mytable m
JOIN airports a ON m.codeIataAirport = a.iata_code
SET m.codeIcaoAirport = a.ident
WHERE m.codeIcaoAirport IS NULL;

3. 保留CTE的正确写法（PostgreSQL适用） #

如果一定要用CTE，需要在CTE中保留目标表的唯一标识（比如id），再通过唯一标识关联更新：

WITH airport_mappings AS (
  SELECT m.id, a.ident
  FROM mytable m
  JOIN airports a ON m.codeIataAirport = a.iata_code
  WHERE m.codeIcaoAirport IS NULL
)
UPDATE mytable m
SET codeIcaoAirport = am.ident
FROM airport_mappings am
WHERE m.id = am.id;

注意事项 #

如果存在一个codeIataAirport对应多条airports记录的情况，需要先处理数据重复（比如用DISTINCT去重，或用MAX()/MIN()指定取哪条值），否则部分数据库会返回更新歧义错误。

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