[postgis-users] Selecting which table column to update

[Francois Hugues]

Hello,

There are a lot of syntax error in your query : the "=" with nothing, the use of case for many columns, the lack of from before your subselect. Hard to say what is the error and what exactly you want to do (at least for me) without knowledge of how your data is made.

I don't understand what value you want to write in the updated column but I am not sure you need to use case but something like this could be ok :

update pipe

set branch_right_id = bob.edge_data.edge_id

from (
select bob.edge_data.edge_id
from bob.edge_data, num_search, pipe            
where st_intersects(bob.edge_data.geom, num_search.the_geom9))  --intersection of max edge collected in pipe table    
and pipe.id = 10
) as foo

where st_centroid(bob.edge_data.geom) >> num_search.the_geom9
and bob.edge_data.edge_id <> pipe.edge
and pipe.id =10

Remember that what you do when you run an update query can not be undone unless you have a save of your original data.

Hugues.


-------- Message d'origine--------
De: postgis-users-bounces at postgis.refractions.net de la part de Bob Pawley
Date: lun. 24/09/2012 19:42
À: postgis-users at postgis.refractions.net
Objet : [postgis-users] Selecting which table column to update
 
Hi

I am attempting to update a table column that is to be determined by a linestring being above, below or to the right of an intersection.

update pipe
    set 
    case when st_centroid(bob.edge_data.geom) >> num_search.the_geom9
        where bob.edge_data.edge_id <> pipe.edge
        and pipe.id =10
        then branch_right_id =
        
        when st_centroid(bob.edge_data.geom) |>> num_search.the_geom9
        bob.edge_data.edge_id <> pipe.edge
        and pipe.id =10
        then branch_up_id =
        
        when st_centroid(bob.edge_data.geom) <<| num_search.the_geom9
        bob.edge_data.edge_id <> pipe.edge
        and pipe.id =10
        then branch_down_id =
        
        end    
    (select bob.edge_data.edge_id
        from bob.edge_data, num_search, pipe            
        where st_intersects(bob.edge_data.geom, num_search.the_geom9))  --intersection of max edge collected in pipe table    
    where pipe.id = 10; 

Following is the error-
ERROR:  syntax error at or near "case"
LINE 3:  case when st_centroid(bob.edge_data.geom) >> num_search.the...

Can this be done?

Bob


-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 3615 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120924/8ecd5f32/attachment.bin>