RE: insert into where not exists - how duplicate key error possible?

Fyi - DB2 supports a merge statement that will only insert if it not exist.

Jim

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Steve
Richter
Sent: Wednesday, April 18, 2018 8:57 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: insert into where not exists - how duplicate key error
possible?

thanks Kevin. I find the distinction very confusing.

I changed the select to group by and the insert worked.

insert into prugdim ( collNum, dimensions, isNewPrice )
with t1 as (
select a.collNum, a.dimensions, max(a.isNewPrice)
from prugload a
where not exists ( select b.collNum
from prugdim b
where a.collNum = b.collNum
and a.dimensions = b.dimensions )
group by a.collnum, a.dimensions )
select a.*
from t1 a


On Wed, Apr 18, 2018 at 8:39 AM, Kevin Bucknum <Kevin@xxxxxxxxxxxxxxxxxxx>
wrote:

Does prugload have the same unique key? Do you have a duplicate in
there? SQL is set based, not row based, so isn't necessarily checking
the not exists before every insert. It gathers all the rows that don't
exist, and then inserts them all.

Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550

-----Original Message-----

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf
Of Steve Richter
Sent: Wednesday, April 18, 2018 7:36 AM
To: Midrange Systems Technical Discussion
Subject: insert into where not exists - how duplicate key error

possible?

why would this SQL fail with a duplicate key error. I am selecting

rows to

insert which do not exist in the "insert into" table.

insert into prugdim ( collNum, dimensions, isNewPrice )
select a.collNum, a.dimensions, a.isNewPrice
from prugload a
where not exists ( select b.collNum
from prugdim b
where a.collNum = b.collNum
and a.dimensions = b.dimensions )

PRUGDIM has primary key of (COLLNUM, DIMENSIONS ). I am inserting
COLLNUM and DIMENSIONS into PRUGDIM from PRUGLOAD where COLLNUM and
DIMENSIONS do not yet exist in PRUGDIM.

I clear the PRUGDIM table. Run the INSERT stmt. Get the message
"duplicate key value specified". Then I run the INSERT stmt a 2nd

time, I get

the message "310 rows inserted".

delete from prugdim
385 rows deleted from PRUGDIM in COURI7.

insert into prugdim ( collNum, dimensions, isNewPrice )
select a.collNum, a.dimensions, a.isNewPrice
from prugload a
where not exists ( select b.collNum
from prugdim b
where a.collNum = b.collNum
and a.dimensions = b.dimensions )

Duplicate key value

specified.

insert into prugdim ( collNum, dimensions, isNewPrice )
select a.collNum, a.dimensions, a.isNewPrice
from prugload a
where not exists ( select b.collNum
from prugdim b
where a.collNum = b.collNum
and a.dimensions = b.dimensions )
310 rows inserted in PRUGDIM
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD