php MYSQL 更新使用 sum() 结果跨多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14470041/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL Update using sum() result across multiple tables
提问by windywah
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_stockcolumn.
它将attributes_stock列中的所有字段组加在一起。
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.
非常感谢任何建议。
回答by Ray
You can't use a group byinside an update statement. You'll need to use an sub select to do the grouping.
您不能group by在更新语句中使用 a 。您需要使用子选择来进行分组。
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
回答by spencer7593
Some favor the newer-style JOIN ... ONsyntax for a join operation, vs. the comma operator and the join predicate in the WHERE clause:
有些人更喜欢JOIN ... ON连接操作的新式语法，而不是逗号运算符和 WHERE 子句中的连接谓词：
UPDATE products p
JOIN ( SELECT q.products_id
, SUM(q.attributes_stock) AS sum_attr
FROM products_attributes q
GROUP BY q.products_id
) r
ON r.products_id = p.products_id
SET p.products_quantity = r.sum_attr
回答by Barranka
Try this:
尝试这个：
update
products,
(select
products_id, sum(attributes_stock) as sumAttr
from products_attributes
group by products_id) as a
set
products.products_cuantity = a.sumAttr
where
products.products_id = a.products_id
