[postgis-users] syntax to pick correct, single result from subquery

[karsten vennemann]

Ok I am fighting with this SQL task:
 
I have a polygon layer "quad" and a point location layer "r_model_cells" in PostGIS. In each of the quads there will be one or more of the pints located. 
Now I would like to update a field 'quad_id' in he point layer "r_model_cells" with the id of the quad it is located within. Thus I can get all point ids and respective quad ids they are via this query
 
select quad.quad_id qid, r_model_cells.record_id rid from ca.quad, ca.r_model_cells 
where st_within(r_model_cells.merc_geom,quad.merc_geom);
 
but how can I use this query to update a column my field quad_id in he point layer. I can'get my snytax together :(
 
I also have this so far
 
update ca.r_model_cells set quad_id = 
(select quad.quad_id from ca.quad, ca.r_model_cells 
where
quad.merc_geom && r_model_cells.merc_geom 
and intersects(quad.merc_geom,r_model_cells.merc_geom));
 
But this query will rerieve more then one records in the subquery ...
Thus I get ERROR:  
more than one row returned by a subquery used as an expression I want to limit to one which correcponds to the point in question....
Any ideas what my syntax should be ?
 
 
Note: Right now I can do it via two queries below 
---------------------------------------------------------------------------------------------------------------------
select quad.quad_id qid, r_model_cells.record_id AS rid 
into temp_rid
from ca.quad, ca.r_model_cells 
where st_within(r_model_cells.merc_geom,quad.merc_geom) ;
 
and then 
 
update ca.r_model_cells set quad_id = 
qid from (select temp_rid.qid qid, temp_rid.rid, r_model_cells.record_id from temp_rid, ca.r_model_cells 
where temp_rid.rid =r_model_cells.record_id ) as bubu ;
---------------------------------------------------------------------------------------------------------------------
 
 
Cheers
Karsten
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110216/fdf1688a/attachment.html>