Re: Postgres update with self join - Mailing list pgsql-general

From Franco Bruno Borghesi
Subject Re: Postgres update with self join
Date
Msg-id 1092170453.1958.4.camel@taz.oficina
Whole thread Raw
In response to Postgres update with self join  ("Igor Kryltsov" <kryltsov@yahoo.com>)
List pgsql-general
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
Attachment

pgsql-general by date:

Previous
From: "Chris Ochs"
Date:
Subject: 7.4.3 server panic
Next
From: Andrew Ayers
Date:
Subject: Re: Problems with MS Visual Basic 6.0