如何编写SQL UPDATE语句按指定条件更新表的active字段

实现逻辑 #

更新需要同时满足两个业务规则要求：

• 产品编号前缀为ATR-（符合主产品判定规则）
• 该产品所属的parent_id分组下总记录数大于1（排除独立产品不修改的场景）

首先通过子查询统计所有组内记录数大于1的parent_id，再关联主表更新符合前缀要求的记录的active字段为0即可。

各数据库实现语句 #

请将以下语句中的product_table替换为你实际使用的表名。

MySQL #

UPDATE product_table t1
INNER JOIN (
    SELECT parent_id
    FROM product_table
    GROUP BY parent_id
    HAVING COUNT(*) > 1
) t2 ON t1.parent_id = t2.parent_id
SET t1.active = 0
WHERE t1.product_number LIKE 'ATR-%';

PostgreSQL #

UPDATE product_table t1
SET active = 0
FROM (
    SELECT parent_id
    FROM product_table
    GROUP BY parent_id
    HAVING COUNT(*) > 1
) t2
WHERE t1.parent_id = t2.parent_id
AND t1.product_number LIKE 'ATR-%';

SQL Server #

UPDATE t1
SET active = 0
FROM product_table t1
INNER JOIN (
    SELECT parent_id
    FROM product_table
    GROUP BY parent_id
    HAVING COUNT(*) > 1
) t2 ON t1.parent_id = t2.parent_id
WHERE t1.product_number LIKE 'ATR-%';

前置验证建议 #

执行更新前可以先执行以下SELECT语句，确认要更新的行是否符合预期：

SELECT * FROM product_table t1
INNER JOIN (
    SELECT parent_id
    FROM product_table
    GROUP BY parent_id
    HAVING COUNT(*) > 1
) t2 ON t1.parent_id = t2.parent_id
WHERE t1.product_number LIKE 'ATR-%';

该语句会筛选出所有待更新的记录，确认无误后再执行UPDATE操作即可。

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