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

Think about it as two queries, or two passes. Row at a time would be
something like this:
read a row from the source, check to see if it exists, if not insert it.
Repeat until all source rows have been read. In this case, if you have
two rows with the same key in the source table, it would skip the second
one because it exists when it does the check.
Set based is this:
Read all the rows from the source table and discard any that already
exist. Insert all the rows that you previously selected. If you have
duplicates in the source table that don't exist, it will select all of
them. At insert time, when it gets to the second duplicate, it dies with
the duplicate key error.

By using a group by, you effectively eliminated the duplicate record.

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:57 AM
To: Midrange Systems Technical Discussion
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