MS SQL同ID下用指定MAT行值更新多行 优化为CTE窗口函数写法

问题修复说明 #

• 原语句存在两个问题：
  1. 未添加非MAT行的过滤条件，导致作为取值源的MAT行也被匹配更新
  2. SET子句存在笔误，两次赋值value2，遗漏了value3的更新逻辑

优化后的CTE+窗口函数实现 #

利用窗口函数直接按ID分组提取MAT行的对应值，无需表自关联，在数据量较大的场景下性能优势更明显：

WITH MatValueCTE AS (
    SELECT
        *,
        -- 按ID分组，提取同组内MAT行的三个value值
        MAX(CASE WHEN TYPE = 'MAT' THEN value1 END) OVER (PARTITION BY ID) AS mat_val1,
        MAX(CASE WHEN TYPE = 'MAT' THEN value2 END) OVER (PARTITION BY ID) AS mat_val2,
        MAX(CASE WHEN TYPE = 'MAT' THEN value3 END) OVER (PARTITION BY ID) AS mat_val3
    FROM #LinkTable
)
UPDATE MatValueCTE
SET 
    value1 = mat_val1,
    value2 = mat_val2,
    value3 = mat_val3
WHERE TYPE <> 'MAT' -- 仅更新非MAT行，彻底避免源数据被误改

写法说明 #

• 无需自关联表，减少IO消耗，性能优于原自关联写法
• 逻辑更直观，CTE中直接完成源值的提取，避免关联条件写错的问题
• 过滤条件明确，完全规避MAT行被误更新的风险
• 执行后结果与预期输出完全一致，MAT行保持原数据不变，同ID其他行的value字段会被MAT行的值覆盖

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