postgresql: group by in column update - syntax error

Question

I'm trying to get the altitude of the heighest point in each area, I wrote this query which in working fine:

select max(obstacle.valhgt_ft) 
from obstacle,
     obstacle_buffer 
where st_contains (obstacle_buffer.geom,obstacle.geom) 
group by obstacle_buffer.primary_id

But when I try to update an attribute with this query I have a syntax error near "group"

UPDATE obstacle_buffer 
    SET max_valhgt_ft = max(obstacle.valhgt_ft) 
from obstacle,
     obstacle_buffer 
where st_contains (obstacle_buffer.geom,obstacle.geom) 
group by obstacle_buffer.primary_id

Answer 1

You can use a co-related sub-query to do this:

UPDATE obstacle_buffer 
    SET max_valhgt_ft = (select max(obstacle.valhgt_ft) 
                         from obstacle 
                         where st_contains (obstacle_buffer.geom, obstacle.geom))