SQL Update重复数据更新异常：多次执行误更新全部记录

原查询存在的核心问题 #

1. 重复判定逻辑错误：子查询仅按secret_key分区，未结合date字段，不符合你定义的“同一secret_key和date对应多条记录”的重复规则；排序字段用secret_key ASC，同一分区内排序值完全相同，ROW_NUMBER()无法有效区分不同行。
2. 条件冲突与字段不一致：子查询中secret_key =1与外层secret_key=123矛盾，属于笔误；日期字段dt_payment_date与外层date字段不统一，需对齐表结构实际字段。
3. 无更新状态限制：未对kp的状态或更新标记做限制，导致每次执行都会反复反转kp的值，第二次执行时会把已处理的行再次反向更新。

修复方案一（基于kp状态限制） #

假设初始kp为正数，通过限制kp>0确保仅更新未被处理过的重复行，避免反复执行时切换数值：

UPDATE records
SET kp = -kp
WHERE av = pm
  AND secret_key = 123
  AND date = '2014-01-21'
  AND kp > 0  -- 仅更新未被反转的行
  AND pm IN (
    SELECT pm
    FROM (
      SELECT pm,
             ROW_NUMBER() OVER(
               PARTITION BY secret_key, date  -- 按secret_key+date分区，匹配重复定义
               ORDER BY id ASC  -- 用主键/唯一字段排序，确保行号唯一
             ) AS row_num
      FROM records
      WHERE secret_key = 123
        AND date = '2014-01-21'
    ) platnosc
    WHERE platnosc.row_num > 1  -- 仅处理重复组中的非首行
  );

如果表中没有主键id，可替换为其他唯一标识字段（如create_time），只要能区分同一重复组内的不同行即可。

修复方案二（新增状态标记字段，更可靠） #

若kp的正负状态无法作为判断依据，可新增一个标记字段记录处理状态，彻底避免重复执行的误操作：

1. 先添加状态字段：

ALTER TABLE records ADD COLUMN is_processed BOOLEAN DEFAULT FALSE;

2. 更新时标记处理状态：

UPDATE records
SET kp = -kp,
    is_processed = TRUE
WHERE av = pm
  AND secret_key = 123
  AND date = '2014-01-21'
  AND is_processed = FALSE  -- 仅处理未标记的行
  AND pm IN (
    SELECT pm
    FROM (
      SELECT pm,
             ROW_NUMBER() OVER(
               PARTITION BY secret_key, date
               ORDER BY id ASC
             ) AS row_num
      FROM records
      WHERE secret_key = 123
        AND date = '2014-01-21'
    ) platnosc
    WHERE platnosc.row_num > 1
  );

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