How to do an udapte with subquery and groupby?-CodePudding

I have 2 tables, my first table named "popa" contains departments, my second table "stat" contains statistics, if I do an inner join of the 2 I get good results that I would like to put in a column "of" my table "popa".

SELECT dep, sum(i_d) FROM popa INNER JOIN stat on popa.num_dep = stat.dep group BY dep

I tried something like

UPDATE popa

SET pourcentagedeces = s from 

(SELECT dep as d, sum(i_d)as s
      FROM popa INNER JOIN stat on popa.num_dep = stat.dep group BY d)

WHERE dep = d

in fact i'm a little lost as you can see i tried wht you said, but it didn't work

enter image description here

Thank you stef

CodePudding user response：

You must use multiple-table UPDATE:

UPDATE popa
JOIN (SELECT dep num_dep, SUM(i_d)as s
      FROM stat 
      GROUP BY num_dep) stats USING (num_dep)
SET popa.pourcentagedeces = stats.s;

CodePudding user response：

As pointed out in the comments this is redundant storage of the aggregates but -

UPDATE `popa`
INNER JOIN (
    SELECT `dep`, SUM(`i_d`) AS `s`
    FROM `stat`
    GROUP BY `dep`
)AS `agg` ON `popa`.`num_dep` = `agg`.`dep`
SET `pourcentagedeces` = `agg`.`s`;

CodePudding user response：

Is this what you need?

You use UPDATE JOIN where one p1 is the table and p2 is the subquery, joined by the condition.

   UPDATE popa p1 JOIN ( SELECT dep, sum(incid_dchosp) as s FROM popa INNER JOIN stat on popa.num_dep = stat.dep group BY dep ) p2 ON p1.dep = p2.dep
 SET p1.pourcentagedeces = p2.s