[SQL] What is wrong with this PostgreSQL UPDATE statement??

[Steve Johnson]

Sorry for the fairly long post.

I'm having a big problem trying to update one table from another in
PostgreSQL 8.3.1.

I have a lookup table called termgroup:

# select * from termgroup;
termgroupname | mindays | maxdays
---+-+-
1-30 days |   1 |  30
31-59 days|  31 |  59
60-89 days|  60 |  89
90-119 days   |  90 | 119
120-179 days  | 120 | 179
180-364 days  | 180 | 364
1-2 years | 365 | 729
2-3 years | 730 |1094
3-4 years |1095 |1459
4-5 years |1460 |1824
5+ years  |1825 |  99
(11 rows)

And also a data table with data that needs to be catagorized using the
above lookup table:

# select * from certgroups;
days | number | termgroupname
--++---
 58 | 66 |
 303 | 11 |
 732 |   1056 |
1096 | 66 |
 25 |123 |
(5 rows)

As you can see from the detailed session below, the update statement
that works perfectly in MS-SQL 2005 fails miserably in PG.

ANY SUGGESTIONS WOULD BE GREATLY APPRECIATED!!

Thanks,
S.

--- START of SQL session:

create table termgroup (
termgroupname varchar(20) not null,
mindays int not null,
maxdays int not null,
CONSTRAINT PKtermgroup_termgroupname PRIMARY KEY (termgroupname)
);
--
insert into termgroup (termgroupname,mindays,maxdays) values ('1-30 days',1,30);
insert into termgroup (termgroupname,mindays,maxdays) values ('31-59
days',31,59);
insert into termgroup (termgroupname,mindays,maxdays) values ('60-89
days',60,89);
insert into termgroup (termgroupname,mindays,maxdays) values ('90-119
days',90,119);
insert into termgroup (termgroupname,mindays,maxdays) values ('120-179
days',120,179);
insert into termgroup (termgroupname,mindays,maxdays) values ('180-364
days',180,364);
insert into termgroup (termgroupname,mindays,maxdays) values ('1-2
years',365,729);
insert into termgroup (termgroupname,mindays,maxdays) values ('2-3
years',730,1094);
insert into termgroup (termgroupname,mindays,maxdays) values ('3-4
years',1095,1459);
insert into termgroup (termgroupname,mindays,maxdays) values ('4-5
years',1460,1824);
insert into termgroup (termgroupname,mindays,maxdays) values ('5+
years',1825,99);
--
select * from termgroup order by mindays;
--

create table certgroups (
days int not null primary key,
number int not null,
termgroupname varchar(20) null);
--
insert into certgroups(days,number) values (25,123);
insert into certgroups(days,number) values (58,66);
insert into certgroups(days,number) values (303,11);
insert into certgroups(days,number) values (732,1056);
insert into certgroups(days,number) values (1096,66);
--
select * from certgroups order by days;
--
update certgroups
set termgroupname = tg.termgroupname
from certgroups c, termgroup tg
where (c.days = tg.mindays) and (c.days = tg.maxdays);
--
select * from certgroups order by days;
--
/*
-- MS-SQL output (CORRECT):
daysnumber  termgroupname
--- --- 
58  66  31-59 days
303 11  180-364 days
732 10562-3 years
109666  3-4 years

(4 row(s) affected)

-- PostgreSQL output (WRONG!!):
days | number | termgroupname
--++---
 58 | 66 | 31-59 days OK
 303 | 11 | 31-59 days WRONG
 732 |   1056 | 31-59 days WRONG
1096 | 66 | 31-59 days WRONG
(4 rows)
*/

insert into certgroups(days,number) values (25,123);
--
update certgroups
set termgroupname = tg.termgroupname
from certgroups c, termgroup tg
where (c.days = tg.mindays) and (c.days = tg.maxdays);
--
select * from certgroups order by days;
--
/*
-- MS-SQL output (CORRECT):
daysnumber  termgroupname
--- --- 
25  123 1-30 days
58  66  31-59 days
303 11  180-364 days
732 10562-3 years
109666  3-4 years

(5 row(s) affected)

-- PostgreSQL output (WRONG!!):
days | number | termgroupname
--++---
 25 |123 | 1-30 daysRIGHT
 58 | 66 | 1-30 daysWRONG
 303 | 11 | 1-30 daysWRONG
 732 |   1056 | 1-30 daysWRONG
1096 | 66 | 1-30 daysWRONG
(5 rows)
*/
--- END of SQL session

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] What is wrong with this PostgreSQL UPDATE statement??

[Stephan Szabo]

On Fri, 22 Aug 2008, Steve Johnson wrote:

 update certgroups
 set termgroupname = tg.termgroupname
 from certgroups c, termgroup tg
 where (c.days = tg.mindays) and (c.days = tg.maxdays);

In recent PostgreSQL versions I believe this is properly written:

update certgroups c
set termgroupname = tg.termgroupname
from termgroup tg
where (c.days = tg.mindays) and (c.days = tg.maxdays);

At least as of SQL2003, I think both of the above use extensions, so
there's no guarantee to the behavior on different systems and to do it
with a standard query, you'd need to use a subselect, something like:

update certgroups c set termgroupname = (select termgroupname from
termgroup tg where (c.days = tg.mindays) and (c.days =tg.maxdays));


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] What is wrong with this PostgreSQL UPDATE statement??

[Tom Lane]

Stephan Szabo [EMAIL PROTECTED] writes:
 On Fri, 22 Aug 2008, Steve Johnson wrote:
 update certgroups
 set termgroupname = tg.termgroupname
 from certgroups c, termgroup tg
 where (c.days = tg.mindays) and (c.days = tg.maxdays);

 In recent PostgreSQL versions I believe this is properly written:
 update certgroups c
 set termgroupname = tg.termgroupname
 from termgroup tg
 where (c.days = tg.mindays) and (c.days = tg.maxdays);

Yeah, in PG's eyes the former is creating a cartesian join between two
versions of certgroups.  I think MSSQL interprets the FROM reference as
being the same as the update target, but we don't.

 At least as of SQL2003, I think both of the above use extensions,

Correct, the standard disallows a FROM clause altogether; and I'm not
sure that they weren't right.  No matter which way you resolve the above
ambiguity, you've still got the problem that the update behavior is
ill-defined if a given target row joins to more than one set of rows
from the other table(s).

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql