MySQL：如何更新孩子的SUM（）值到同一个表中的父行？

Question

我需要获取我的子行的总和值并更新父行上的数据。 我已经得到了如下所示，但遇到错误＃1111（无效的组功能使用）。

CREATE TEMPORARY TABLE cms_ladu_temp LIKE cms_ladu; 

INSERT INTO cms_ladu_temp SELECT * FROM cms_ladu WHERE parent_id IN (326,500); 

UPDATE 
    cms_ladu 
INNER JOIN 
    cms_ladu_temp 
ON 
    cms_ladu_temp.parent_id=cms_ladu.id 
SET 
    cms_ladu.child_packaging_units_in=SUM(cms_ladu_temp.packaging_units_in), 
    cms_ladu.child_net_weight_in=SUM(cms_ladu_temp.net_weight_in) 
WHERE 
    cms_ladu.id IN (326,500); 

Answer 1

你需要做的是按ID分组，以分隔每个父母身份的子女总和。您将需要使用子选择来完成此操作。

另外，您不需要创建临时表。只是包装在一个子查询（由parent_id分组）的聚集，并加入到子选择主表：

UPDATE 
    cms_ladu a 
INNER JOIN 
(
    SELECT parent_id, 
      SUM(packaging_units_in) AS pui, 
      SUM(net_weight_in) AS nwi 
    FROM  cms_ladu 
    WHERE parent_id IN (326,500) 
    GROUP BY parent_id 
) b ON a.id = b.parent_id 
SET 
    a.child_packaging_units_in = b.pui, 
    a.child_net_weight_in = b.nwi 

Answer 2

尝试

UPDATE cms_ladu a 
    INNER JOIN (SELECT parent_id, SUM(packaging_units_in) AS packaging_units_in, SUM(net_weight_in) AS net_weight_in FROM cms_ladu_temp WHERE id IN (326,500) GROUP BY parent_id) b 
     ON b.parent_id=a.id 
SET  a.child_packaging_units_in = b.packaging_units_in 
    ,a.child_net_weight_in = b.net_weight_in