Update table using contains function

Question

I have a table that containing placename regions and another table of roads. I have another table that containing centroid of both placename region and roads.The table structure of third table is placename,Roadname,x,y. I want to update the placename field of the roads which are passing through the corresponding placename region. I used the following query:

Update haryana set placename = placename_region.name from placename_region where haryana.the_geom in (select st_centroid(ch_roads.the_geom) from ch_roads,placename_region where st_contains(placename_region.the_geom,ch_roads.the_geom))

But it went wrong. wrong placenames updated in placename field. Please help me to solve this

Answer 1

It doesn't work because that's not the proper way of making a UPDATE from a SELECT , You are doing something else...

Something like this should work, although I don't understand this part of the query:

haryana.the_geom in (select st_centroid(ch_roads.the_geom) ....)

or as I rephrased it:

st_centroid(ch_roads.the_geom) = haryana.the_geom

here is the full query:

Update haryana set placename = placename_region.name from placename_region,ch_roads where st_contains(placename_region.the_geom,ch_roads.the_geom) && st_centroid(ch_roads.the_geom) = haryana.the_geom;