在多个表中使用sum()结果进行MYSQL更新 [英] MYSQL Update using sum() result across multiple tables

问题描述

这点效果很好:

 SELECT products_id, sum(attributes_stock) 
 FROM products_attributes 
 GROUP BY products_id

将attributes_stock列中的所有字段组加在一起.

Which adds together all the groups of fields in the attributes_stock column.

我遇到的麻烦是得到这个结果来更新另一个表中的另一列.

What I am having trouble with is getting this result to UPDATE another column in another table.

这就是我所拥有的:

 UPDATE products, products_attributes 
 SET products.products_quantity = sum(products_attributes.attributes_stock) GROUP BY products_attributes.products_id 
 WHERE products.products_id = products_attributes.products_id

任何建议，我们将不胜感激.

Any advice greatly appreciated.

推荐答案

您不能在更新语句中使用group by.您需要使用子选择进行分组.

You can't use a group by inside an update statement. You'll need to use an sub select to do the grouping.

类似这样的东西:

UPDATE products p,( SELECT products_id, sum(attributes_stock)  as mysum
                   FROM products_attributes GROUP BY products_id) as s

   SET p.products_quantity = s.mysum
  WHERE p.products_id = s.products_id

这篇关于在多个表中使用sum()结果进行MYSQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！