Update all rows affected by a group by

Question

I have the following query using PostgreSql :

SELECT SUM(table.a) 
FROM table 
GROUP BY table.b
HAVING SUM(table.a) > x;

And now I need to update a column in all rows affected by the precedent query.

I tried the following solution :

UPDATE table 
SET c = 'value' 
WHERE (SELECT SUM(table.a) 
       FROM table 
       GROUP BY table.b) > x;

but I get the following error

More than a line returned by a sub-request used as an expression

I cannot find a solution to update a column in all rows affected by a group by. If anyone can show me the way, it would be greatly appreciated.

Please also tag the database engine you’re using, they all have differences in their syntax and it helps to know which one to target — Sami Kuhmonen, Jul 30 '18 at 07:47

Goran Kutlaca · Accepted Answer · 2018-07-30T07:48:40.340

2

You want to update table rows only for those rows that have table.b in the list of values of those whose sums of table.a column values exceeded your defined x

So, I believe you want to use this:

UPDATE TABLE
   SET c = 'value'
 WHERE b IN (SELECT b
               FROM TABLE
              GROUP BY table.b
             HAVING SUM (table.a) > x));

edited Jul 30 '18 at 07:48

answered Jul 30 '18 at 07:45

Goran Kutlaca

2,014
1
12
18

1

You could simplify this by just returning b from the innermost query and dropping the middle one – Sami Kuhmonen Jul 30 '18 at 07:46
1

@SamiKuhmonen You're absolutely right! Thank you for your input :) – Goran Kutlaca Jul 30 '18 at 07:49

Update all rows affected by a group by

1 Answers1