473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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
Nov 23 '05 #1
4 7615

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
"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a...
3
by: Narine | last post by:
Hi All, I need to write one complicated update statement and I'm looking at maybe finding a simpler way to do it. I have 2 tables: 1.Photo Table PhotoID FileName 1 111.jpg
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
9
by: Vorpal | last post by:
Here is a small sample of data from a table of about 500 rows (Using MSSqlserver 2000) EntryTime Speed Gross Net ------------------ ----- ----- 21:09:13.310 0 0 0 21:09:19.370 9000 ...
3
by: chrisspen | last post by:
Is there a way to loop through all instantiated objects and update their classes when a source file changes? I know about Michael Hudson's method...
2
by: Greg Stark | last post by:
I find I often want to be able to do joins against views where the view are aggregates on a column that has an index. Ie, something like SELECT a.*, v.n FROM a JOIN (select a_id,count(*) as n...
18
by: Chris Travers | last post by:
Hi all; I have been looking into how to ensure that synchronous replication, etc. could best be implimented. To date, I see only two options: incorporate the replication code into the database...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
10
by: Hank | last post by:
We have just recently migrated the data from our Access 2000 backend to Postgres. All forms and reports seem to run correctly but, in many cases, very slowly. We do not want to switch over until...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.