[postgis-users] Update from spatial query problem

[Ulises F-Troche]

Hi group,

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:

table1 (areas):
- id (polygon code)
- name (polygon name)
- geom (polygon)

table2 (non spatial table for persons)
- code (person id)
- name (person name)
- lastname (person last name)
- area (area name as appears in table1)

table3 (person location)
- code (person id)
- geom (point)

I need to update table2.area (which is null) based on the location of each point in table3.  The following query provides the location information required...

select table3.*,table1.name
from table3,table1
where st_within(table3.geom,table1.geom);

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...

update table2
set area=mytable.name
from (select table1.name
from
table3,table1
where st_within(table3.geom,table1.geom)) AS mytable;

Any help to figure this out is appreciated.

Thanks,

Ulises
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130103/5741b477/attachment.html>