Thread: Postgres update with self join

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

On Tue, 10 Aug 2004, Igor Kryltsov wrote:

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

You probably need to be constraining the join between test and (i1 join
i2).

Maybe an additional where clause like "and test.name=i2.name" or something
like that would work.

Or, I think you can do this with a subselect which would have the
advantage of not requiring extensions to the standard.  Perhaps something
like
 update test set code=(select code from test i2 where test.master=i2.name)
 where code=0;
would do it.

"Igor Kryltsov" <kryltsov@yahoo.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

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

Thank you very much.

This works:
UPDATE

test

SET

code=T2.code

FROM

test T2

WHERE

test.code=0 AND

test.master=T2.name;



Igor