Postgresql how to update a column for max values for each row?

Question

So I have a tablea that I need to upgrade the column highestprog with values from the table called buffer

If a point intersects with 2 buffers, one with 80 progression and another with 90 progression, the column should be updated with a 90.

So I thought the max operator should be used here. My query was as follows:

UPDATE test.tablea

SET highestprog = (SELECT max(b.progression) FROM test.tablea a, test.buffer b WHERE ST_Contains(b.geom, a.geom))

However, this just updates every single row in the entire table with a 100, instead of the correct value for each row. How to update with the correct value from the right buffer?

ewcz · Accepted Answer · 2017-03-08 19:15:50Z

13

If I understand your question correctly, the maximum should be taken per point. Assuming that the table tablea contains an "id column" idx, one might proceed as:

WITH stat AS (
    SELECT a.idx, MAX(b.progression) AS maxprog
    FROM
    test.tablea a, test.buffer b
    WHERE ST_Contains(b.geom, a.geom)
    GROUP BY a.idx
)   
UPDATE test.tablea
SET highestprog = stat.maxprog
FROM stat
WHERE test.tablea.idx = stat.idx

answered Mar 8, 2017 at 19:15

ewcz

13.1k1 gold badge30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ruben Over a year ago

Using the temporary table is a clever solution, thanks!

Collectives™ on Stack Overflow

Postgresql how to update a column for max values for each row?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related