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;

Answer 1

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.)

Answer 2

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)