[postgis-users] UPDATE table to a joined query...

[Mike Leahy]

Hello all,

This question is related to updating tables - is there any way to calculate or 
update the values in a column in a table to the values in a field produced by 
a query result?  An example of what I'm trying to do is below:

update (tbl_ind_mananas LEFT JOIN (select count(*) as count, (dubicacion || 
zona || manzana) as cod_manzana from tbl_censo_poblacion_1993 group by 
dubicacion, zona, manzana) tbl1 on relacion = cod_manzana) as tbl2 set 
poblacion = count;

Basically I have a table (tbl_ind_manzanas) with a unique code (relacion) that 
can be linked to a field in the query result (cod_manzana).  I want to update 
a field in the table with the count(*) result in the query.  The update 
statement (as I have attempted it above) doesn't work...it seems that I can do 
nothing but directly update the values in a table.  As soon as I try to supply 
anything other than just a table to the update statement, it doesn't like it.

I'm sure others have experience with this issue...it seems to me that there 
would be many cases where such an approach would be useful.  I'm hoping 
there's another method that I might be able to use that could accomplish 
essentially the same result.  Any suggestions are greatly appreciated.

Kind regards,
Mike

----------------------------------------
This mail sent through www.mywaterloo.ca