这个位工作得很好:
SELECT products_id, sum(attributes_stock)
FROM products_attributes
GROUP BY products_id
它将attributes_stock
列中的所有字段组相加。
我遇到的问题是让这个结果更新另一个表中的另一列。
这是我所拥有的:
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
任何建议都非常感谢。
不能在更新语句中使用group by
。 您需要使用子选择进行分组。
像这样:
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
有些人更喜欢连接操作的较新样式的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
试试这个:
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