CODEWITHSUNDEEP
sqlpostgresqlpostgis
Luffydude
Luffydude

Reputation: 772

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

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?

Upvotes: 2

Views: 6542

Answers (1)

ewcz
ewcz

Reputation: 13087

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

Upvotes: 13

Related Questions