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.