含GROUP BY与SUM的同表UPDATE语句问题求助

需求：

• 针对product表执行更新操作：当某个parent_id对应的所有子项（即parent_id等于该值的行）的stock字段总和为0时，将id等于此parent_id的父项的active字段设为0。

表结构说明：

• 包含字段：id（必存在，唯一标识）、parent_id（可选，关联父项的id）、stock（库存值）、active（状态字段）
• parent_id的值在id字段中仅出现一次，但在parent_id字段中可多次出现（即一个父项可以对应多个子项）

已尝试的错误写法及问题 #

写法1： #

UPDATE product AS b1, (
   SELECT HEX(`parent_id`) AS par_id, SUM(stock) AS total_stock
   FROM product
   GROUP BY par_id
   HAVING total_stock = 0 ) AS b2
SET b1.active = 0
WHERE b1.id = b2.par_id

执行成功，但影响0行

写法2： #

UPDATE product AS p1
INNER JOIN
(SELECT HEX(`parent_id`) AS par_id, SUM(stock) AS total_stock
FROM product AS p2 GROUP BY par_id HAVING total_stock = 0) AS ts
ON p1.id = ts.par_id
SET p1.active = 0

执行成功，但影响0行

写法3： #

UPDATE product
SET active = 0
FOR (SELECT parent_id FROM product GROUP BY parent_id HAVING SUM(stock) = 0) = id;

语法错误，子查询返回多行结果，且FOR关键字使用错误

正确解决方案 #

错误原因分析 #

前两种写法的核心问题是使用了HEX()函数转换parent_id，如果parent_id本身不是十六进制字符串类型（比如是整数），转换后的值无法和id匹配，导致没有行被更新。

可行SQL语句 #

方法1：JOIN关联更新

UPDATE product AS parent
JOIN (
    SELECT parent_id, SUM(stock) AS total_stock
    FROM product
    WHERE parent_id IS NOT NULL  -- 仅统计有父项的子行，提升效率
    GROUP BY parent_id
    HAVING total_stock = 0
) AS child_summary ON parent.id = child_summary.parent_id
SET parent.active = 0;

方法2：子查询匹配更新

UPDATE product
SET active = 0
WHERE id IN (
    SELECT parent_id
    FROM product
    WHERE parent_id IS NOT NULL
    GROUP BY parent_id
    HAVING SUM(stock) = 0
);

补充说明 #

• 加入WHERE parent_id IS NOT NULL可以过滤掉无父项的顶级行，避免无效统计；若业务需要处理parent_id为空的场景，可直接移除该条件。
• 两种写法均保证只更新符合条件的父项，不会误操作子项或其他无关行。

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