Update using a subquery with aggregates and groupby in Postgres

Question

I'm trying to update a column in a table with the max value of that column grouped by another column.

So for example, say we have a table named transactions with two columns: quantity and item_name. And for whatever reason we want to set quantity equal to the maximum quantity found for each item_name.

I'm pretty stumped and bad at doing things like this in SQL, but here's what I have so far:

UPDATE transactions 
SET
quantity = subquery.quantity
FROM (select max(quantity), item_name
      from transaction group by item_name) AS subquery
WHERE  and item_name = subquery.item_name;

Erwin Brandstetter · Accepted Answer · 2014-03-05T18:17:01.073

5

In addition to your syntax errors that @Gordon already pointed out, it is regularly a good idea to exclude empty updates:

UPDATE transaction t
SET    quantity = sub.max_quantity
FROM  (
   SELECT item_name, max(quantity) AS max_quantity
   FROM   transaction
   GROUP  BY 1
   ) sub
WHERE t.item_name = sub.item_name
AND  t.quantity IS DISTINCT FROM sub.max_quantity;

No need to write new row versions (at almost full cost) without changing anything. (Except if you want to fire a trigger.)

edited Mar 05 '14 at 18:17

answered Mar 05 '14 at 18:11

Erwin Brandstetter

605,456
145
1,078
1,228

Nailed it, thanks! And I like that distinct at the end, thats pretty awesome – Gus Mar 05 '14 at 18:19
1

@Gus: I assume you only have one table `transaction` - there were two variants in your original: `transactions`, `transaction`. I picked the singular variant, because I prefer those for table names. – Erwin Brandstetter Mar 05 '14 at 18:21

Gordon Linoff · Answer 2 · 2014-03-05T18:04:18.290

2

You are actually quite close:

UPDATE transactions 
    SET quantity = subquery.quantity
    FROM (select max(quantity) as quantity, item_name
          from transactions
          group by item_name
         ) subquery
    WHERE transactions.item_name = subquery.item_name;

I just removed the and in where and and renamed the table in the subquery to transactions.

edited Mar 05 '14 at 18:04

answered Mar 05 '14 at 17:44

Gordon Linoff

1,242,037
58
646
786

Sorry about the typos. I'm getting an issue right now where its telling me subquery.quantity doesn't exist – Gus Mar 05 '14 at 18:03
@Gus . . . Missed that one. – Gordon Linoff Mar 05 '14 at 18:04

score 1 · Answer 3 · answered Mar 05 '14 at 17:46

1

Try this, it should also work for you. It would evaluate the quantity for each and every row and may be slower than Gordon's answer.

UPDATE transactions 
SET quantity = (SELECT MAX(quantity) 
                 FROM transaction as t 
                 WHERE t.item_name = transactions.item_name)

answered Mar 05 '14 at 17:46

user2989408

3,127
1
17
15

Correlated subueries are generally much more expensive in this context. – Erwin Brandstetter Mar 05 '14 at 18:16

Update using a subquery with aggregates and groupby in Postgres

3 Answers3

Linked