By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,852 Members | 994 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,852 IT Pros & Developers. It's quick & easy.

Postgres update with self join

P: n/a
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
Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

P: n/a
"Igor Kryltsov" <kr******@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

P: n/a
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 | 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



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


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (FreeBSD)

iD8DBQBBGTLV21dVnhLsBV0RAh/lAKCFOxkWS00s5MC6INxikQmz7IuWMwCeOpSz
K4sZqg9TiBuutznfXtjyVNo=
=mZ/M
-----END PGP SIGNATURE-----

Nov 23 '05 #4

P: n/a
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
Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.