<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div style="font-family: 'times new roman', 'new york', times, serif; font-size: 12pt;"><span>Thanks for the suggestion, Steve, but when I ran your suggested statement, it fail with...</span></div><div style="font-family: 'times new roman', 'new york', times, serif; font-size: 16px; color: rgb(0, 0, 0); background-color: transparent; font-style: normal;"><span><br></span></div><div style="background-color: transparent;"><span><div style="background-color: transparent;">ERROR: columnn mytable.code do not exists</div><div style="background-color: transparent;">LINE 6: where mytable.code=table2.code;</div><div style="background-color: transparent;"> ^</div><div style="background-color: transparent;"><br></div><div style="background-color: transparent;">********** Error
**********</div><div style="background-color: transparent;"><br></div><div style="background-color: transparent;">ERROR: no existe la columna mytable.code</div><div style="background-color: transparent;">SQL state: 42703</div><div style="background-color: transparent;">Character: 191</div><div style="color: rgb(0, 0, 0); font-family: 'times new roman', 'new york', times, serif; font-size: 16px; font-style: normal; background-color: transparent;"><br></div></span></div><div style="font-family: 'times new roman', 'new york', times, serif; font-size: 12pt;"><br></div> <div style="font-family: 'times new roman', 'new york', times, serif; font-size: 12pt;"> <div style="font-family: 'times new roman', 'new york', times, serif; font-size: 12pt;"> <div dir="ltr"> <font size="2" face="Arial"> <hr size="1"> <b><span style="font-weight:bold;">From:</span></b> Stephen Woodbridge <woodbri@swoodbridge.com><br> <b><span style="font-weight:
bold;">To:</span></b> postgis-users@lists.osgeo.org <br> <b><span style="font-weight: bold;">Sent:</span></b> Thursday, January 3, 2013 2:39 PM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: [postgis-users] Update from spatial query problem<br> </font> </div> <br>
On 1/3/2013 2:27 PM, Ulises F-Troche wrote:<br>> Hi group,<br>><br>> I'm new to PostGIS and so far I'm really liking it. I'm trying to<br>> update a table based on a spatial query but I have not been able to do<br>> it successfully. I have 3 tables as follow:<br>><br>> table1 (areas):<br>> - id (polygon code)<br>> - name (polygon name)<br>> - geom (polygon)<br>><br>> table2 (non spatial table for persons)<br>> - code (person id)<br>> - name (person name)<br>> - lastname (person last name)<br>> - area (area name as appears in table1)<br>><br>> table3 (person location)<br>> - code (person id)<br>> - geom (point)<br>><br>> I need to update table2.area (which is null) based on the location of<br>> each point in table3. The following query provides the location<br>> information required...<br>><br>> select table3.*,table1.name<br>> from table3,table1<br>> where
st_within(table3.geom,table1.geom);<br>><br>> but when try to create the update statement it is updating all records<br>> with the same value (area name), which is the same of the first record<br>> returned by the selection query...<br>><br>> update table2<br>> set area=mytable.name<br>> from (select <a target="_blank" href="http://table1.name/">table1.name</a> <http://table1.name/><br>> from<br>> table3,table1<br>> where st_within(table3.geom,table1.geom)) AS mytable;<br><br>Your update has no qualifier that says "What record int table2 should <br>get updated" so all records are getting updated.<br><br>This might give you better results:<br><br>update table2 set area=mytable.name<br>from (select table1.name<br> from table3, table1<br> where st_within(table3.geom,table1.geom)) AS mytable<br>where mytable.code=table2.code;<br><br>-Steve<br><br><br>> Any help
to figure this out is appreciated.<br>><br>> Thanks,<br>><br>> Ulises<br>><br>><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users<br>><br><br>_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br><br><br> </div> </div> </div></body></html>