sum 两张表 mysql,在多个表之间使用sum（）结果进行MYSQL更新

This bits working great:

SELECT products_id, sum(attributes_stock)

FROM products_attributes

GROUP BY products_id

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.

This is what I have:

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.

解决方案

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

Something like this:

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