Re: Postgres update with self join

This is the way you do it in postgreSQL:

UPDATE
   test
SET
   code=T2.code
FROM
   test T2
WHERE
   test.code=0 AND
   test.master=T2.name;

you need to specify the join condition in the WHERE clause.

On Tue, 2004-08-10 at 00:34, Igor Kryltsov wrote:

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 | ABCABC2 |     0 | ABCEKL  | 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 | ABCABC2 | 15074 | ABCEKL  | 15074 |EKL1 | 15074 | EKL
(5 rows)

... all values 15074.


Thank you,


Igor



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your     joining column's datatypes do not match