<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Karsten,<br>
<br>
try (I hope I made no mistake...): <br>
<br>
<font color="#000080" size="2"><span class="784354608-16022011">update
ca.r_model_cells a set quad_id =b.quad_id from </span></font><br>
<font color="#000080" size="2"><span class="784354608-16022011">(select
quad.quad_id</span></font><font color="#000080" face="Arial"
size="2"><span class="784354608-16022011">,
r_model_cells.record_id rid</span></font><font color="#000080"
size="2"><span class="784354608-16022011"> from</span></font><font
color="#000080" size="2"><span class="784354608-16022011">
ca.r_model_cells</span></font><font color="#000080" size="2"><span
class="784354608-16022011"> inner join</span></font><font
color="#000080" size="2"><span class="784354608-16022011"></span></font><font
color="#000080" size="2"><span class="784354608-16022011"> ca.quad</span></font><br>
<font color="#000080" size="2"><span class="784354608-16022011">on </span></font><font
color="#000080" size="2"><span class="784354608-16022011">quad.merc_geom
&& r_model_cells.merc_geom</span></font><br>
<font color="#000080" size="2"><span class="784354608-16022011">where</span></font><font
color="#000080" size="2"><span class="784354608-16022011"></span></font><font
color="#000080" size="2"><span class="784354608-16022011"> </span></font><font
color="#000080" face="Arial" size="2"><span
class="784354608-16022011">
st_within(r_model_cells.merc_geom,quad.merc_geom)</span></font><font
color="#000080" size="2"><span class="784354608-16022011">) b<br>
where a.record_id=b.rid;</span></font><br>
<br>
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...<br>
<br>
Regards,<br>
<br>
Birgit.<br>
<br>
On 16.02.2011 10:03, karsten vennemann wrote:
<blockquote cite="mid:395A6C77731C40CDACD4CE3C5AAC40DF@snuggie"
type="cite">
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
<meta name="GENERATOR" content="MSHTML 8.00.6001.19019">
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">Ok I am fighting with this SQL
task:</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font> </div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">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.
</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">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</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font> </div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">select quad.quad_id qid,
r_model_cells.record_id rid from ca.quad, ca.r_model_cells <br>
where st_within(r_model_cells.merc_geom,quad.merc_geom);</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font> </div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">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 :(</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font> </div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">I also have this so far</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font> </div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">update ca.r_model_cells set
quad_id = <br>
(select quad.quad_id from ca.quad, ca.r_model_cells <br>
where<br>
quad.merc_geom && r_model_cells.merc_geom <br>
and intersects(quad.merc_geom,r_model_cells.merc_geom));</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font> </div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">But this query will rerieve more
then one records in the subquery ...</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">Thus I get ERROR: </span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">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....</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"><font color="#000080"
face="Arial" size="2"><span class="784354608-16022011">Any
ideas what my syntax should be ?</span></font></span></font></div>
<div>
<div><font color="#000080" face="Arial" size="2"><font
color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font> </font></div>
<div><font color="#000080" face="Arial" size="2"><font
color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font> </font></div>
<div><font color="#000080" face="Arial" size="2"><font
color="#000080" face="Arial" size="2"><span
class="784354608-16022011">Note: </span></font></font><font
color="#000080" face="Arial" size="2"><span
class="784354608-16022011">Right now I can do it via two
queries below </span></font></div>
</div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font><font
color="#000080" face="Arial" size="2"><span
class="784354608-16022011"><span class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">select quad.quad_id qid,
r_model_cells.record_id AS rid <br>
into temp_rid<br>
from ca.quad, ca.r_model_cells <br>
where st_within(r_model_cells.merc_geom,quad.merc_geom) ;</span></font></div>
<div><span class="784354608-16022011"></span> </div>
<div><font face="Arial"><font color="#000080"><font size="2">a<span
class="784354608-16022011">nd then </span></font></font></font></div>
<div><font face="Arial"><font color="#000080"><font size="2"><span
class="784354608-16022011"></span></font></font></font> </div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">update ca.r_model_cells set
quad_id = <br>
qid from (select temp_rid.qid qid, temp_rid.rid,
r_model_cells.record_id from temp_rid, ca.r_model_cells <br>
where temp_rid.rid =r_model_cells.record_id ) as bubu ;</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"><span class="784354608-16022011"><span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---<span class="784354608-16022011">---<span
class="784354608-16022011">---</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font> </div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011"></span></font> </div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">Cheers</span></font></div>
<div><font color="#000080" face="Arial" size="2"><span
class="784354608-16022011">Karsten</span></font></div>
<div> </div>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>