如何在UPDATE语句中利用别名，通过id IN批量更新数据

核心思路 #

把原单条更新的重复子查询改为关联查询+聚合的模式，只需在一处指定批量ID，就能完成所有目标记录的更新，不用重复修改参数。

优化后的批量更新SQL（通用兼容版） #

UPDATE kontakt ko
JOIN (
    -- 将audit表中同一记录的多个字段值聚合为行数据
    SELECT 
        `key` AS record_id,
        MAX(CASE WHEN cname = 'STRASSE1' AND old IS NOT NULL THEN old END) AS strasse1_val,
        MAX(CASE WHEN cname = 'PLZ' AND old IS NOT NULL THEN old END) AS plz_val,
        MAX(CASE WHEN cname = 'ORT' AND old IS NOT NULL THEN old END) AS ort_val
    FROM audit
    WHERE `key` IN ('56454', '56546', '962881') -- 此处填写所有需要更新的ID
    GROUP BY `key`
) audit_data ON ko.id = audit_data.record_id
SET 
    ko.Strasse1 = audit_data.strasse1_val,
    ko.plz = audit_data.plz_val,
    ko.ort = audit_data.ort_val;

COMMIT;

写法说明 #

1. 聚合子查询：用CASE+MAX把audit表中同一key下不同cname的old值转成列，避免多次重复查询audit表，提升效率。
2. 关联更新：通过JOIN关联kontakt的id和audit的key，保证每条kontakt记录能匹配到对应的audit数据。
3. 批量ID管理：仅需在子查询的IN子句里一次性填完所有要更新的ID，不用逐个修改参数。
4. 逻辑一致性：如果audit中某条记录的特定字段无有效数据（old为NULL），对应的kontakt字段会被设为NULL，和原单条更新逻辑完全一致。

针对特定数据库的简化写法（以PostgreSQL为例） #

如果使用PostgreSQL，也可以用多表关联的方式实现：

UPDATE kontakt ko
SET 
    Strasse1 = audit_strasse.old,
    plz = audit_plz.old,
    ort = audit_ort.old
FROM 
    (SELECT `key`, old FROM audit WHERE cname = 'STRASSE1' AND old IS NOT NULL) audit_strasse,
    (SELECT `key`, old FROM audit WHERE cname = 'PLZ' AND old IS NOT NULL) audit_plz,
    (SELECT `key`, old FROM audit WHERE cname = 'ORT' AND old IS NOT NULL) audit_ort
WHERE 
    ko.id IN ('56454', '56546', '962881')
    AND ko.id = audit_strasse.`key`
    AND ko.id = audit_plz.`key`
    AND ko.id = audit_ort.`key`;

COMMIT;

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