如何编写UPDATE查询为表中重复记录的workorder字段添加基于计数的自动编号

要实现把重复的workorder值更新为带递增编号的新值，我们可以借助窗口函数给每个重复分组的记录分配行号，再通过拼接字符串生成新编号。下面分主流数据库给出具体方案：

适用于SQL Server的UPDATE语句 #

WITH ranked_orders AS (
    SELECT 
        workorder,
        -- 给每个相同workorder的记录分配递增行号
        ROW_NUMBER() OVER (PARTITION BY workorder ORDER BY (SELECT NULL)) AS row_num,
        id -- 替换成你的表的唯一主键，用来精准定位要更新的行
    FROM engineering_job_schedule
    WHERE workorder IS NOT NULL
)
UPDATE ranked_orders
SET workorder = CONCAT(
    LEFT(workorder, LEN(workorder) - 6), -- 截取编号前的固定前缀部分
    RIGHT('00000' + CAST(row_num AS VARCHAR(6)), 6) -- 把行号格式化为6位带前导零的字符串
)
WHERE row_num > 1; -- 只更新重复的第2条及以后的记录，保留第一条原编号

适用于PostgreSQL的UPDATE语句 #

PostgreSQL的UPDATE语法略有不同，需要关联CTE来定位目标行：

WITH ranked_orders AS (
    SELECT 
        workorder,
        ROW_NUMBER() OVER (PARTITION BY workorder ORDER BY NULL) AS row_num,
        id -- 替换为表的唯一主键
    FROM engineering_job_schedule
    WHERE workorder IS NOT NULL
)
UPDATE engineering_job_schedule
SET workorder = CONCAT(
    SUBSTRING(workorder FROM 1 FOR LENGTH(workorder) - 6),
    lpad(row_num::text, 6, '0') -- 格式化6位带前导零的编号
)
FROM ranked_orders
WHERE engineering_job_schedule.id = ranked_orders.id
AND ranked_orders.row_num > 1;

关键注意事项 #

• 必须有唯一主键：比如示例中的id，这是精准更新每一行的核心，没有主键极易出现批量更新错误。
• 编号位数调整：如果你的workorder后缀不是固定6位数字，需要调整LEN(workorder)-6或者SUBSTRING里的长度参数，可以先通过RIGHT(workorder, 6)确认后缀长度。
• 排序规则自定义：如果需要按特定顺序分配编号（比如按记录创建时间），把ORDER BY (SELECT NULL)换成对应的字段，比如ORDER BY create_time ASC。

执行完成后，你的表会得到期望的结果：

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