× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.