Re: Postgres update with self join

"Igor Kryltsov" <kryltsov(at)yahoo(dot)com> writes:
> 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.

> 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;

That query has no join condition to the target table, so it's hardly
surprising that it updates everything in sight. If it "works" in MSSQL
it must be because they are making some weird decision to pretend that
one or the other of the mentions of test in the FROM clause ought to be
identified with the target table. (If it acts as you want then they
must be identifying "test i2" with "test", which is *really* weird ---
you would think the first occurrence of test in the FROM would be the
one they'd pick. I suppose this is another instance of an
implementation bug becoming enshrined as a feature.)

In Postgres you want to do something like this:

update test
set code = mst.code
from test mst
where test.master = mst.name
and test.code = 0;

To act exactly as you stated in words you'd probably also want to add
"and mst.code <> 0".

regards, tom lane