多UPDATE消除重复列SQL简化后失效的原因及优化方案咨询

我原本编写了7条串行的UPDATE语句，能正常消除tble表中offer_id_01到offer_id_08列的重复值（允许offer_id_08为NULL）：

update tble
set 
offer_id_02 = offer_id_03,
offer_id_03 = offer_id_04,
offer_id_04 = offer_id_05,
offer_id_05 = offer_id_06,
offer_id_06 = offer_id_07,
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_02 = offer_id_01;

update tble
set 
offer_id_03 = offer_id_04,
offer_id_04 = offer_id_05,
offer_id_05 = offer_id_06,
offer_id_06 = offer_id_07,
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_03 = offer_id_01 or offer_id_03 = offer_id_02; 

update tble
set 
offer_id_04 = offer_id_05,
offer_id_05 = offer_id_06,
offer_id_06 = offer_id_07,
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_04 = offer_id_01 or offer_id_04 = offer_id_02 or offer_id_04 = offer_id_03;

update tble
set 
offer_id_05 = offer_id_06,
offer_id_06 = offer_id_07,
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_05 = offer_id_01 or offer_id_05 = offer_id_02 or offer_id_05 = offer_id_03 or offer_id_05 = offer_id_04;

update tble
set 
offer_id_06 = offer_id_07,
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_06 = offer_id_01 or offer_id_06 = offer_id_02 or offer_id_06 = offer_id_03 or offer_id_06 = offer_id_04 or offer_id_06 = offer_id_05;

update tble
set 
offer_id_07 = offer_id_08,
offer_id_08 = NULL
where offer_id_07 = offer_id_01 or offer_id_07 = offer_id_02 or offer_id_07 = offer_id_03 or offer_id_07 = offer_id_04 or offer_id_07 = offer_id_05  or offer_id_07 = offer_id_06;

update tble
set 
offer_id_08 = NULL
where offer_id_08 = offer_id_01 or offer_id_08 = offer_id_02 or offer_id_08 = offer_id_03 or offer_id_08 = offer_id_04 or offer_id_08 = offer_id_05 or offer_id_08 = offer_id_06 or offer_id_08 = offer_id_07;

但尝试简化为单条带CASE语句的UPDATE后，重复值仅转移到其他列（比如offer_id_01与offer_id_02的重复变为offer_id_02与offer_id_03的重复），无法达到预期效果：

UPDATE tble
SET
    OFFER_ID_02 = CASE 
                    WHEN offer_id_02 = offer_id_01 THEN offer_id_03
                    ELSE offer_id_02
                  END,
    OFFER_ID_03 = CASE 
                    WHEN offer_id_03 = offer_id_01 OR offer_id_03 = offer_id_02 THEN offer_id_04 
                    ELSE offer_id_03
                  END,
    OFFER_ID_04 = CASE 
                    WHEN offer_id_04 = offer_id_01 OR offer_id_04 = offer_id_02 OR offer_id_04 = offer_id_03 THEN offer_id_05 
                    ELSE offer_id_04
                  END,
    OFFER_ID_05 = CASE 
                    WHEN offer_id_05 = offer_id_01 OR offer_id_05 = offer_id_02 OR offer_id_05 = offer_id_03 OR offer_id_05 = offer_id_04 THEN offer_id_06 
                    ELSE offer_id_05
                  END,
    OFFER_ID_06 = CASE 
                    WHEN offer_id_06 = offer_id_01 OR offer_id_06 = offer_id_02 OR offer_id_06 = offer_id_03 OR offer_id_06 = offer_id_04 OR offer_id_06 = offer_id_05 THEN offer_id_07 
                    ELSE offer_id_06
                  END,
    OFFER_ID_07 = CASE 
                    WHEN offer_id_07 = offer_id_01 OR offer_id_07 = offer_id_02 OR offer_id_07 = offer_id_03 OR offer_id_07 = offer_id_04 OR offer_id_07 = offer_id_05 OR offer_id_07 = offer_id_06 THEN offer_id_08 
                    ELSE offer_id_07
                  END,
    OFFER_ID_08 = CASE 
                    WHEN offer_id_08 = offer_id_01 OR offer_id_08 = offer_id_02 OR offer_id_08 = offer_id_03 OR offer_id_08 = offer_id_04 OR offer_id_08 = offer_id_05 OR offer_id_08 = offer_id_06 OR offer_id_08 = offer_id_07 THEN null
                    ELSE offer_id_08
                  END

一、简化逻辑失效的原因 #

• 原始7条UPDATE的执行逻辑：这7条语句是串行逐次执行，每一条UPDATE处理完对应列的重复值后，后续的UPDATE会基于前一次更新后的表数据进行判断和操作。相当于把重复值逐步向后“推送”，最终将重复值挤到最后一列并置空，彻底消除重复。
• 单条CASE UPDATE的问题：SQL中UPDATE语句的SET子句里，所有列的赋值逻辑基于当前行的原始数据（即使某列先被赋值，后续CASE判断中引用的列仍然是更新前的原始值）。比如当offer_id_02被更新为原始offer_id_03的值后，offer_id_03的CASE判断仍然使用原始的offer_id_02值（和offer_id_01重复的那个），不会触发更新，导致新的重复值（更新后的offer_id_02和原始offer_id_03）出现，重复值只是被转移而非消除。

二、可行的SQL简化方案 #

方案1：使用CTE锁定原始值，同步计算所有列的新值 #

通过CTE预先获取每行的所有原始offer_id值，确保所有CASE判断和赋值都基于同一套原始数据，避免更新过程中的值干扰。示例（以MySQL为例）：

WITH original_values AS (
    SELECT 
        id, -- 假设表有主键id用于关联
        offer_id_01,
        offer_id_02,
        offer_id_03,
        offer_id_04,
        offer_id_05,
        offer_id_06,
        offer_id_07,
        offer_id_08
    FROM tble
)
UPDATE tble
JOIN original_values ov ON tble.id = ov.id
SET
    offer_id_02 = CASE 
                    WHEN ov.offer_id_02 = ov.offer_id_01 THEN ov.offer_id_03
                    ELSE ov.offer_id_02
                  END,
    offer_id_03 = CASE 
                    WHEN ov.offer_id_03 = ov.offer_id_01 OR ov.offer_id_03 = ov.offer_id_02 THEN ov.offer_id_04 
                    ELSE ov.offer_id_03
                  END,
    offer_id_04 = CASE 
                    WHEN ov.offer_id_04 = ov.offer_id_01 OR ov.offer_id_04 = ov.offer_id_02 OR ov.offer_id_04 = ov.offer_id_03 THEN ov.offer_id_05 
                    ELSE ov.offer_id_04
                  END,
    offer_id_05 = CASE 
                    WHEN ov.offer_id_05 = ov.offer_id_01 OR ov.offer_id_05 = ov.offer_id_02 OR ov.offer_id_05 = ov.offer_id_03 OR ov.offer_id_05 = ov.offer_id_04 THEN ov.offer_id_06 
                    ELSE ov.offer_id_05
                  END,
    offer_id_06 = CASE 
                    WHEN ov.offer_id_06 = ov.offer_id_01 OR ov.offer_id_06 = ov.offer_id_02 OR ov.offer_id_06 = ov.offer_id_03 OR ov.offer_id_06 = ov.offer_id_04 OR ov.offer_id_06 = ov.offer_id_05 THEN ov.offer_id_07 
                    ELSE ov.offer_id_06
                  END,
    offer_id_07 = CASE 
                    WHEN ov.offer_id_07 = ov.offer_id_01 OR ov.offer_id_07 = ov.offer_id_02 OR ov.offer_id_07 = ov.offer_id_03 OR ov.offer_id_07 = ov.offer_id_04 OR ov.offer_id_07 = ov.offer_id_05 OR ov.offer_id_07 = ov.offer_id_06 THEN ov.offer_id_08 
                    ELSE ov.offer_id_07
                  END,
    offer_id_08 = CASE 
                    WHEN ov.offer_id_08 = ov.offer_id_01 OR ov.offer_id_08 = ov.offer_id_02 OR ov.offer_id_08 = ov.offer_id_03 OR ov.offer_id_08 = ov.offer_id_04 OR ov.offer_id_08 = ov.offer_id_05 OR ov.offer_id_08 = ov.offer_id_06 OR ov.offer_id_08 = ov.offer_id_07 THEN NULL
                    ELSE ov.offer_id_08
                  END;

方案2：行转列去重后再列转行（更彻底的简化） #

将同一行的offer_id列转换为多行数据，去重后重新排列回列的形式，自动填充非重复值，不足的列设为NULL。这种方案逻辑更清晰，适合处理任意数量的重复值：

WITH unpivoted AS (
    SELECT 
        id,
        value,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY seq) AS new_seq
    FROM (
        SELECT id, offer_id_01 AS value, 1 AS seq FROM tble
        UNION ALL
        SELECT id, offer_id_02, 2 FROM tble
        UNION ALL
        SELECT id, offer_id_03, 3 FROM tble
        UNION ALL
        SELECT id, offer_id_04, 4 FROM tble
        UNION ALL
        SELECT id, offer_id_05, 5 FROM tble
        UNION ALL
        SELECT id, offer_id_06, 6 FROM tble
        UNION ALL
        SELECT id, offer_id_07, 7 FROM tble
        UNION ALL
        SELECT id, offer_id_08, 8 FROM tble
    ) AS u
    WHERE value IS NOT NULL -- 先排除NULL值
    GROUP BY id, value -- 按行去重
),
pivoted AS (
    SELECT 
        id,
        MAX(CASE WHEN new_seq = 1 THEN value END) AS offer_id_01,
        MAX(CASE WHEN new_seq = 2 THEN value END) AS offer_id_02,
        MAX(CASE WHEN new_seq = 3 THEN value END) AS offer_id_03,
        MAX(CASE WHEN new_seq = 4 THEN value END) AS offer_id_04,
        MAX(CASE WHEN new_seq = 5 THEN value END) AS offer_id_05,
        MAX(CASE WHEN new_seq = 6 THEN value END) AS offer_id_06,
        MAX(CASE WHEN new_seq = 7 THEN value END) AS offer_id_07,
        MAX(CASE WHEN new_seq = 8 THEN value END) AS offer_id_08
    FROM unpivoted
    GROUP BY id
)
UPDATE tble
JOIN pivoted p ON tble.id = p.id
SET
    tble.offer_id_01 = p.offer_id_01,
    tble.offer_id_02 = p.offer_id_02,
    tble.offer_id_03 = p.offer_id_03,
    tble.offer_id_04 = p.offer_id_04,
    tble.offer_id_05 = p.offer_id_05,
    tble.offer_id_06 = p.offer_id_06,
    tble.offer_id_07 = p.offer_id_07,
    tble.offer_id_08 = p.offer_id_08;

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