Insert problem

From: "A(dot) R(dot) Van Hook" <hook(at)lota(dot)us>
To: pgsql-sql(at)postgresql(dot)org
Subject: Insert problem
Date: 2008-03-09 21:02:53
Message-ID: 47D4507D.8010709@lota.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a table where some of the entries of should have
been posted to the wrong cusid and item#:

cusid | item# |
1747 | 149 | 9251 : Jonathon Krinke .......
1747 | 150 | 10071 : Lindsey Steele ........
1747 | 151 | 10187 : Lindsay Stapley .......
1747 | 152 | 10188 : Amanda Goucher ........
1888 | 0 | 10071 : PROM 75P1206 mi.........

corrected:

1747 | 148 | 9250 : Jonathon Krinke ........
1747 | 149 | 9251 : Jonathon Krinke .......
1888 | 0 | 10071 : PROM 75P1206 mi......
1888 | 1 | 10071 : Lindsey Steele
1888 | 2 | 10187 : Lindsay Stapley
1888 | 3 | 10188 : Amanda Goucher

my correction script:

The script worked fine untill I tried in on the following entries:

1841 | 2 | 9228 : Caty Case : SO:Bra:.....
1841 | 3 | 9621 : Kelsie Greenlee : PROM.....

2072 | 3 | null : Cookie Jared Cook :..........
2072 | 4 | null : Cookie Jared Cook :........
2072 | 5 | null : Cookie Jared Cook :........

insert into schItem (scid, value, iflag, outalts, sidate, istid)
select 2072, i.value, i.iflag, i.outalts, i.sidate, i.istid
from schItem i, schItem s
where i.scid=1841 and i.item >= 3
and s.scid = 2072 group by i.value,
i.iflag, i.outalts, i.sidate, i.istid, s.scid

ERROR: duplicate key violates unique constraint "schitem_pkey"

It seems I have something wrong with the select portion of the script
but I can figure it out.
Any idea??
thanks

ps
table defination

create sequence schItem_item_seq
create table schItem
(scid int NOT NULL references schedule ON DELETE CASCADE,
item int NOT NULL default nextval('schItem_item_seq'),
value text,
iflag int,
outalts int,
sidate date,
istid int references stock,
primary key (scid, item))

--
Arthur R. Van Hook

hook(at)lota(dot)us
avanhook3(at)comcast(dot)net

(816) 578-4704 - Home
(816) 629-0071 Cell

--
Arthur R. Van Hook

hook(at)lota(dot)us
avanhook3(at)comcast(dot)net

(816) 578-4704 - Home
(816) 629-0071 Cell

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jamie Tufnell 2008-03-10 00:14:12 Re: Insert problem
Previous Message Jamie Tufnell 2008-03-09 15:27:08 Re: Composite UNIQUE across two tables?