如何通过SQL更新重复账户类型的指定行至account_type=3

针对需求——对myTable中同一ID下存在重复account_type的行，仅将其中一行的account_type更新为3，其余保留原值，以下是可行的解决方案：

方案1：使用窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

通过ROW_NUMBER()窗口函数为每个(ID, account_type)分组的行编号，仅更新分组内非首行的记录：

WITH ranked_rows AS (
    SELECT 
        primaryKey,
        ID,
        account_type,
        ROW_NUMBER() OVER (PARTITION BY ID, account_type ORDER BY primaryKey) AS rn
    FROM myTable
)
UPDATE myTable
SET account_type = 3
WHERE primaryKey IN (
    SELECT primaryKey 
    FROM ranked_rows 
    WHERE rn > 1 AND account_type != 3
);

逻辑说明： #

1. 用CTEranked_rows给每个(ID, account_type)分组的行按主键排序并编号
2. 筛选出分组内编号大于1的行（即同一组中除首行外的重复行），且排除原本account_type已为3的记录
3. 更新这些行的account_type为3

方案2：关联子查询（适用于不支持窗口函数的旧版MySQL） #

通过关联子查询定位到同一(ID, account_type)分组内的非首行记录：

UPDATE myTable p1
SET account_type = 3
WHERE EXISTS (
    SELECT 1
    FROM myTable p2
    WHERE p2.ID = p1.ID 
      AND p2.account_type = p1.account_type
      AND p2.primaryKey < p1.primaryKey
)
AND p1.account_type != 3;

逻辑说明： #

1. 关联子查询判断当前行是否存在同一ID、同一account_type且主键更小的记录（即当前行不是分组内的首行）
2. 排除原本account_type已为3的记录，避免无效更新
3. 满足条件的行将account_type设为3

原语句错误分析 #

• 第一条SQL的子查询未关联当前行的ID和account_type，只要表中存在任意重复分组，所有行都会被匹配更新
• 第二条SQL的分组逻辑与关联条件混乱，无法精准定位目标行，导致更新范围错误

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