如何在SQL中按指定条件更新已新增列的记录？

期望结果表 #

已新增默认值为Null的New Column列，需按以下规则更新：

• 若同一Value下同时存在Type为A和B的记录，将该Value下所有Type为A的记录的New Column设为A_B
• 若同一Value下同时存在Type为A和C的记录，将该Value下所有Type为A的记录的New Column设为A_C

实现方案 #

核心思路：先识别每个Value对应的匹配类型组合，再关联回原表批量更新Type为A的记录。以下是主流数据库的实现代码：

1. MySQL 版本 #

UPDATE your_table t1
JOIN (
    SELECT 
        Value,
        CASE
            WHEN EXISTS (SELECT 1 FROM your_table WHERE Value = t.Value AND Type = 'B') THEN 'A_B'
            WHEN EXISTS (SELECT 1 FROM your_table WHERE Value = t.Value AND Type = 'C') THEN 'A_C'
        END AS new_val
    FROM your_table t
    WHERE Type = 'A'
    GROUP BY Value
) t2 ON t1.Value = t2.Value AND t1.Type = 'A'
SET t1.`New Column` = t2.new_val;

2. SQL Server 版本 #

UPDATE t1
SET t1.[New Column] = t2.new_val
FROM your_table t1
JOIN (
    SELECT 
        Value,
        CASE
            WHEN EXISTS (SELECT 1 FROM your_table WHERE Value = t.Value AND Type = 'B') THEN 'A_B'
            WHEN EXISTS (SELECT 1 FROM your_table WHERE Value = t.Value AND Type = 'C') THEN 'A_C'
        END AS new_val
    FROM your_table t
    WHERE Type = 'A'
    GROUP BY Value
) t2 ON t1.Value = t2.Value AND t1.Type = 'A';

3. PostgreSQL 版本 #

UPDATE your_table t1
SET "New Column" = t2.new_val
FROM (
    SELECT 
        Value,
        CASE
            WHEN EXISTS (SELECT 1 FROM your_table WHERE Value = t.Value AND Type = 'B') THEN 'A_B'
            WHEN EXISTS (SELECT 1 FROM your_table WHERE Value = t.Value AND Type = 'C') THEN 'A_C'
        END AS new_val
    FROM your_table t
    WHERE Type = 'A'
    GROUP BY Value
) t2
WHERE t1.Value = t2.Value AND t1.Type = 'A';

逻辑说明 #

• 子查询先筛选出所有Type为A的Value，并判断每个Value是否存在对应的B或C类型记录，生成目标更新值
• 主查询通过Value和Type = 'A'关联子查询，批量更新符合条件的记录
• 若某个Value下同时存在A、B、C三种类型，会优先匹配第一条规则（设为A_B），符合需求顺序

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