Postgres update with self join - Mailing list pgsql-general

Hi,


If you can help me to correct my mistake.
To simplify my question:

I have table:

create table test (
name varchar(10),
code integer,
master varchar(10));

I have values:
insert into test values ('ABC', 15074, null);
insert into test values ('ABC1', 0, 'ABC');
insert into test values ('ABC2', 0, 'ABC');
insert into test values ('EKL', 15075, null);
insert into test values ('EKL1', 0, 'EKL');


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;


 select * from test;
 name | code  | master
------+-------+--------
 ABC  | 15074 |
 ABC1 | 15074 | ABC
 ABC2 | 15074 | ABC
 EKL  | 15074 |
 EKL1 | 15074 | EKL
(5 rows)

... all values 15074.


Thank you,


Igor