如何将重复记录的sequence字段更新为连续递增新值

我需要编写SQL查询，找出同一id下sequence列存在重复值的行，并将这些行的sequence更新为按id分组的连续递增数值。

初始数据 #

目标数据 #

我写了下面的脚本，但它会把所有存在重复的id对应的sequence都改成1，达不到预期效果：

WITH DuplicateRecords AS (
   SELECT id, 
          sequence,
          ROW_NUMBER() OVER (PARTITION BY id, sequence ORDER BY (SELECT NULL)) AS RowNum
   FROM table1
   WHERE Id IN (
      SELECT id
      FROM table1
      GROUP BY id, sequence
      HAVING COUNT(*) > 1
   )
),
UPDATE table1
SET sequence = DR.RowNum
FROM table1
JOIN DuplicateRecords DR ON table1.id = DR.id
WHERE table1.id IN (
   SELECT id
   FROM table1
   GROUP BY id, sequence
   HAVING COUNT(*) > 1
)

问题分析 #

你的脚本问题出在两个地方：

1. PARTITION BY id, sequence会把同一id和相同sequence的行单独分组，生成的RowNum只是在重复的sequence内部计数，不是整个id分组的连续序列。
2. JOIN条件只匹配了id，没有唯一标识每一行，导致更新时可能出现行匹配错误。

正确解决方案 #

方案1：为所有id分组重新生成连续sequence（覆盖所有行）

这个方案会直接给每个id下的行分配1、2、3...的连续sequence，不管原来的sequence是否重复，适合彻底统一sequence规则：

WITH UpdatedSequence AS (
    SELECT 
        id,
        row_id, -- 替换为表的唯一主键，比如自增ID
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS new_sequence
    FROM table1
)
UPDATE t1
SET sequence = us.new_sequence
FROM table1 t1
JOIN UpdatedSequence us 
    ON t1.id = us.id 
    AND t1.row_id = us.row_id -- 必须用唯一主键匹配，避免更新错误

方案2：仅更新存在sequence重复的id分组

如果只需要处理那些存在重复sequence的id，先筛选出这些id再更新：

WITH DuplicateIds AS (
    -- 找出存在重复sequence的id
    SELECT id
    FROM table1
    GROUP BY id, sequence
    HAVING COUNT(*) > 1
),
UpdatedSequence AS (
    SELECT 
        id,
        row_id, -- 替换为表的唯一主键
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS new_sequence
    FROM table1
    WHERE id IN (SELECT id FROM DuplicateIds)
)
UPDATE t1
SET sequence = us.new_sequence
FROM table1 t1
JOIN UpdatedSequence us 
    ON t1.id = us.id 
    AND t1.row_id = us.row_id

重要提示 #

• ORDER BY (SELECT NULL)表示不指定特定排序顺序，如果需要按某列（比如data1、记录创建时间）排序来生成sequence，可以替换成实际列名，比如ORDER BY data1 DESC。
• 必须使用表的唯一主键来匹配行，否则同一id下的行可能会被错误匹配，导致sequence值混乱。

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