[postgis-users] update a table

[Obe, Regina]

You seem to be missing some join clauses here so your query looks like
it would be trying to update a cartesian product of records which could
be huge depending on the number of records you are talking about in each
table.  Basically would be (a number of records) * (b number of records)
* (obj_cadangan_oil number of records)
 
What is the connection between the table obj_cadangan_oil and the other
tables - because the below at best will set all records to the same
value.  If that is your intension, then that would be fine.
 
I think your statement should be something like
 
UPDATE obj_cadangan_oil
    SET working_area = m.block_name
FROM  (select block_name, (maybe some other field here you will use to
join) FROM map_field a, map_petroleum_contract_area b where
intersects(a.the_geom,b.the_geom) and b.the_geom ~ a.the_geom)  As m
WHERE obj_cadangan_oil.something = m.something or some spatial condition
 
Hope that helps,
Regina

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bayu
Kurniawan R
Sent: Tuesday, June 05, 2007 2:47 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] update a table


i want update a table, 

when i want to update, a wrote sql like :

UPDATE obj_cadangan_oil set working_area=(select block_name where
intersects(a.the_geom,b.the_geom) and b.the_geom ~ a.the_geom) 
FROM map_field a, map_petroleum_contract_area b; 

it's above query is right ???, 
why it's take a long time to query

-- 
Bayu Kurniawan R
GIS Programmer
PT. Sigma Cipta Utama
Jl. Tekno I Blok B No:5,6,7
BSD City Tangerang 15314 
Indonesia



-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070605/5c0070e4/attachment.html>