SQL Server中存在重复hash_key时如何更新记录？

初始数据 #

目标状态 #

需求说明 #

• 当记录的hash_key相同但hash_diff不同时：
  • 将旧记录（created_date更早的记录）的active_ind设为0，expiry_date设为当前日期，updated_date设为当前日期
  • 将新记录（created_date最晚的记录）的updated_date设为当前日期，保持active_ind=1和expiry_date=12/31/3030不变

错误的尝试SQL #

UPDATE DW.dbo.stg_table_name
WHERE hash_key is duplicated for the single record but the hash_difs are different
SET active_ind = 0 WHERE active_ind = 1
AND where expiry_date = 12/31/3030
SET expiry_date = GETDATE()

问题：语法错误，无法区分新旧记录，逻辑不清晰

解决方案 #

可以通过窗口函数ROW_NUMBER()按hash_key分组，根据created_date倒序排序标记新旧记录，实现批量更新：

方法1：使用CTE（公用表表达式） #

WITH ranked_records AS (
    SELECT 
        dw_id,
        hash_key,
        hash_diff,
        active_ind,
        expiry_date,
        updated_date,
        created_date,
        -- 按hash_key分组，created_date倒序，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY hash_key ORDER BY created_date DESC) AS rn
    FROM DW.dbo.stg_table_name
    -- 只处理存在不同hash_diff的hash_key组
    WHERE hash_key IN (
        SELECT hash_key
        FROM DW.dbo.stg_table_name
        GROUP BY hash_key
        HAVING COUNT(DISTINCT hash_diff) > 1
    )
)
UPDATE ranked_records
SET 
    active_ind = CASE WHEN rn > 1 THEN 0 ELSE active_ind END,
    expiry_date = CASE WHEN rn > 1 THEN GETDATE() ELSE expiry_date END,
    updated_date = GETDATE();

方法说明 #

1. ROW_NUMBER()为每个hash_key组内的记录排序，最新记录rn=1，旧记录rn>1
2. 通过CASE语句分别处理新旧记录的字段更新逻辑
3. CTE的UPDATE会直接作用于原表，一次性完成批量更新
4. 子查询过滤出需要处理的hash_key组，避免无意义的全表扫描

方法2：使用UPDATE FROM子句 #

如果偏好不用CTE，也可以用关联子查询实现：

UPDATE t1
SET 
    active_ind = CASE WHEN t1.created_date < t2.latest_created THEN 0 ELSE t1.active_ind END,
    expiry_date = CASE WHEN t1.created_date < t2.latest_created THEN GETDATE() ELSE t1.expiry_date END,
    updated_date = GETDATE()
FROM DW.dbo.stg_table_name t1
JOIN (
    SELECT hash_key, MAX(created_date) AS latest_created
    FROM DW.dbo.stg_table_name
    GROUP BY hash_key
    HAVING COUNT(DISTINCT hash_diff) > 1
) t2 ON t1.hash_key = t2.hash_key;

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