SQL批量更新：将同一Person ID下低Product ID记录设为非活跃

需求说明 #

需批量处理以下规则的数据库记录：

• 仅操作由user创建且代码为123的数据
• 同一Person ID下，将Product ID数值更小的记录的active字段设为false

实现SQL（以MySQL为例） #

更新语句 #

UPDATE your_table t
JOIN (
    SELECT 
        Person_ID,
        MAX(Product_ID) AS max_product_id
    FROM your_table
    WHERE created_by = 'user' AND code = '123'
    GROUP BY Person_ID
) AS sub ON t.Person_ID = sub.Person_ID
SET t.active = FALSE
WHERE t.created_by = 'user' AND t.code = '123'
AND t.Product_ID != sub.max_product_id;

验证语句（执行更新前建议先确认） #

SELECT t.*
FROM your_table t
JOIN (
    SELECT 
        Person_ID,
        MAX(Product_ID) AS max_product_id
    FROM your_table
    WHERE created_by = 'user' AND code = '123'
    GROUP BY Person_ID
) AS sub ON t.Person_ID = sub.Person_ID
WHERE t.created_by = 'user' AND t.code = '123'
AND t.Product_ID != sub.max_product_id;

语句解释 #

1. 子查询通过GROUP BY Person_ID，筛选出每个符合条件的Person ID对应的最大Product ID
2. 主查询通过JOIN关联原表，定位到每个Person ID下Product ID不等于最大值的记录，将其active字段设为false
3. 重复添加过滤条件，确保仅处理目标范围的数据

其他数据库适配（以PostgreSQL为例） #

UPDATE your_table
SET active = FALSE
WHERE (Person_ID, Product_ID) NOT IN (
    SELECT Person_ID, MAX(Product_ID)
    FROM your_table
    WHERE created_by = 'user' AND code = '123'
    GROUP BY Person_ID
)
AND created_by = 'user' AND code = '123';

注意事项 #

• 替换语句中的your_table为实际表名，created_by、code为对应实际字段名
• 执行更新前务必用验证语句确认待修改记录，避免误操作

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