Re: UPDATE table to a joined query...

On Wed, Oct 15, 2003 at 22:39:39 -0400,
Mike Leahy <mgleahy(at)fes(dot)uwaterloo(dot)ca> wrote:
> 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.

A similar question was asked in the last week.

You want to do the join in the from item list and than join the table
being updated to the join from the from item list in the where clause.

Something like:
update a set a.count = c.count from (select a left join b) as c
where a.id = c.id;