mysql 使用 SUM 更新子查询

Question

您能指导我为什么下面的查询不起作用吗？

谢谢

UPDATE campaigns SET available='0' WHERE product_id IN (SELECT product_id, sum(stock_qty) AS qty FROM `sku_colors_sizes` GROUP BY product_id HAVING sum(stock_qty) < 1) 

Answer 1

您必须从子查询中仅选择一列才能使用 IN 谓词进行比较，您可以对其进行修改，以便仅选择 product_id，如下所示:

UPDATE campaigns 
SET available='0'
WHERE product_id IN (SELECT product_id -- <=====
                     FROM
                     (
                       SELECT product_id, sum(stock_qty) AS qty 
                       FROM sku_colors_sizes 
                       GROUP BY product_id 
                       HAVING sum(stock_qty) < 1
                     ) AS t)