<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div><div style="font-size: 12pt;">Hi group,</div><div style="font-size: 12pt;"><br></div><div style="background-color: transparent;">I'm new to PostGIS and so far I'm really liking it. I'm trying to update a table based on a spatial query but I have not been able to do it successfully. I have 3 tables as follow:</div><div style="background-color: transparent;"><br></div><div style="background-color: transparent;">table1 (areas):</div><div style="background-color: transparent;">- id (<span class="yshortcuts" id="lw_1357240824_0">polygon</span> code)</div><div style="background-color: transparent;">- name (polygon name)</div><div style="background-color: transparent;">- geom (polygon)</div><div style="background-color: transparent;"><br></div><div style="background-color: transparent;">table2 (non spatial table
for persons)</div><div style="background-color: transparent;">- code (person id)</div><div style="background-color: transparent;">- name (person name)</div><div style="background-color: transparent;">- lastname (person last name)</div><div style="background-color: transparent;">- area (area name as appears in table1)</div><div style="background-color: transparent;"><br></div><div style="background-color: transparent;">table3 (person location)</div><div style="background-color: transparent;">- code (person id)</div><div style="background-color: transparent;">- geom (point)</div><div style="background-color: transparent;"><br></div><div style="background-color: transparent;">I need to update table2.area (which is null) based on the location of each point in table3. <span style="background-color: transparent;">The following query provides the location information required...</span></div><div style="background-color: transparent;"><br></div><div
style="background-color: transparent;"><div style="background-color: transparent;"><font face="times new roman, new york, times, serif">select table3.*,table1.name</font></div><div style="background-color: transparent;"><font face="times new roman, new york, times, serif">from table3,table1</font></div><div style="background-color: transparent;"><font face="times new roman, new york, times, serif">where st_within(table3.geom,table1.geom);</font></div><div style="background-color: transparent;"><font face="times new roman, new york, times, serif"><br></font></div><div style="background-color: transparent;"><font face="times new roman, new york, times, serif">but when try to create the update statement it is updating all records with the same value (area name), which is the same of the first record returned by the selection query...</font></div><div style="background-color: transparent;"><font face="times new roman, new york, times,
serif"><br></font></div><div style="background-color: transparent;"><font face="times new roman, new york, times, serif"><div style="background-color: transparent;">update table2</div><div style="background-color: transparent;">set area=mytable.name</div><div style="background-color: transparent;">from (select <a target="_blank" href="http://table1.name/" style="color: rgb(40, 98, 197); outline: 0px;"><span class="yshortcuts" id="lw_1357240824_1">table1.name</span></a></div><div style="background-color: transparent;">from</div><div style="background-color: transparent;">table3,table1</div><div style="background-color: transparent;">where st_within(table3.geom,table1.geom)) AS mytable;</div><div style="background-color: transparent;"><br></div><div style="background-color: transparent;">Any help to figure this out is appreciated.</div><div style="background-color: transparent;"><br></div><div style="background-color: transparent;">Thanks,</div><div
style="background-color: transparent;"><br></div><div style="background-color: transparent;">Ulises</div><div><br></div></font></div></div></div></div></body></html>