Postgres update with self join

From: "Igor Kryltsov" <kryltsov(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Postgres update with self join
Date: 2004-08-10 03:34:54
Message-ID: cf9fnn$2ac1$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Liam Lesboch 2004-08-10 03:43:50 Replication options?
Previous Message Scott Marlowe 2004-08-10 03:03:59 Re: Losing records when server hang