Re: Postgres update with self join

On Tue, 10 Aug 2004, Igor Kryltsov wrote:

> Table looks like:
>
> select * from test;
>  name | code  | master
> ------+-------+--------
>  ABC  | 15074 |
>  ABC1 |     0 | ABC
>  ABC2 |     0 | ABC
>  EKL  | 15075 |
>  EKL1 |     0 | EKL
> (5 rows)
>
>
> Now I need to replace "0" values in "code" column by corresponding "code"
> values by following link between "master" field in a record where code=0 and
> "name" field where it is not.
> By the other words first two 0's have to be replaced with 15074 and last 0
> with 15075.
>
> This update works in MSSQL but in Postgres it replaces code values as shown
> below.
>
> update test
> set code = i1.code
> from test i1
> join test i2 on i1.name = i2.master
> where i2.code = 0;

You probably need to be constraining the join between test and (i1 join
i2).

Maybe an additional where clause like "and test.name=i2.name" or something
like that would work.

Or, I think you can do this with a subselect which would have the
advantage of not requiring extensions to the standard.  Perhaps something
like
 update test set code=(select code from test i2 where test.master=i2.name)
 where code=0;
would do it.