postgreSQL update all column rows with subselect without link between tables. Can I avoid plpgsql?

Question

This SQL gives me a list of IDs and the point-in-polygon count.

select count(*), poly.gid 
from inpn p 
INNER JOIN half_degree poly ON 
    poly.the_geom && p.the_geom and CONTAINS(poly.the_geom, p.the_geom)
group by poly.gid

count   gid
10       1
30       2

..

Now I want to update a table (half_degree) column with the count information.

update half_degree 
set inpn_count = (
    select count(*) 
    from inpn p 
    INNER JOIN half_degree poly ON 
        poly.the_geom && p.the_geom 
        and CONTAINS(poly.the_geom, p.the_geom)
    )

but this updates inpn_count with the TOTAL COUNT of points in all polygons. Then, if I add an group by count...

update half_degree 
set inpn_count=(
    select count(*) as i 
    from inpn p 
    INNER JOIN half_degree poly ON 
        poly.the_geom && p.the_geom
        and CONTAINS(poly.the_geom,p.the_geom) 
    group by i
    )

ERROR: Agreggate not authorised inside a GROUP BY

What can I do if i don't have a link between both tables? (to add a WHERE clause in the update sql). Can I avoid using PLPGSQL?

Answer 1

update half_degree poly
set inpn_count = p.total
from (
    select count(*) as total, p.the_geom
    from inpn p 
    INNER JOIN half_degree poly ON 
        poly.the_geom && p.the_geom and CONTAINS(poly.the_geom, p.the_geom)
    group by p.the_geom
    ) p
where poly.the_geom && p.the_geom and CONTAINS(poly.the_geom, p.the_geom)