× 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.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.