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

[Birgit Laggner]

  Hi Karsten,

try (I hope I made no mistake...):

update ca.r_model_cells a set quad_id =b.quad_id from
(select quad.quad_id, r_model_cells.record_id rid from ca.r_model_cells 
inner join ca.quad
on quad.merc_geom && r_model_cells.merc_geom
where st_within(r_model_cells.merc_geom,quad.merc_geom)) b
where a.record_id=b.rid;

You have to relate the results of the subquery to some row identificator 
in the target table, otherwise it tries to write all results of the 
subquery into every row of the target table...

Regards,

Birgit.

On 16.02.2011 10:03, karsten vennemann wrote:
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110216/7873c76d/attachment.html>