How to do an udapte with subquery and groupby?

user_14955028 0
Viewed 40

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

mysql sql
3 Answers

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;
user_101387340

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`;
user_11912470

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
user_8884720
Related