如何在Snowflake中使用UPDATE语句更新完全重复记录

问题背景 #

ABT表存在完全重复的记录，要求仅通过UPDATE语句将每组重复记录中的一条的Duplicate字段更新为'D'，其余重复记录保持NULL。

更新前数据 #

预期更新结果 #

已通过以下SELECT语句筛选出待更新的重复记录，但无法转换为UPDATE操作：

SELECT id, pname  from 
(
  select ROW_NUMBER() OVER (PARTITION BY id ORDER BY cdate desc ) as dup, A.* from ABT A  
)
where dup <>1 ;

可行的UPDATE方案 #

方案1：借助CTE（公共表表达式）实现更新 #

这种方式兼容性强，适用于大多数主流数据库（如SQL Server、PostgreSQL、Oracle 12c+）：

WITH TaggedRecords AS (
    SELECT 
        *,
        -- 按完全重复的字段分组，给每组记录编号
        ROW_NUMBER() OVER (
            PARTITION BY id, pname, cdate 
            ORDER BY (SELECT NULL) -- 无特定排序需求时用此占位，若有唯一标识字段可替换为该字段
        ) AS record_seq
    FROM ABT
)
UPDATE TaggedRecords
SET Duplicate = 'D'
WHERE record_seq > 1; -- 更新每组中除第一条外的记录

方案2：子查询关联原表更新 #

若数据库不支持CTE，可使用子查询关联的方式：

UPDATE ABT
SET Duplicate = 'D'
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT 
            id, pname, cdate,
            ROW_NUMBER() OVER (PARTITION BY id, pname, cdate ORDER BY (SELECT NULL)) AS record_seq
        FROM ABT
    ) t
    WHERE t.id = ABT.id 
      AND t.pname = ABT.pname 
      AND t.cdate = ABT.cdate 
      AND t.record_seq > 1
      -- 若表有唯一标识字段（如rowid、主键），需添加对应匹配条件确保精准定位
);

注意：如果你的表存在唯一标识字段（比如自增主键、数据库内置的行标识如Oracle的ROWID、MySQL的_rowid），将ORDER BY (SELECT NULL)替换为该字段排序，能让更新的目标记录更稳定可控。

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