AW: SQL issue with EXISTS not "working"

Have you tried an exception join (instead of the NOT EXISTS)?

Insert into COMMON_FILE
Select a.*
From File_A a Exception Join COMMON_FILE b on a.ITEM = b.ITEM

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jim
It
Gesendet: Friday, 24.10 2014 21:07
An: Midrange Systems Technical Discussion
Betreff: RE: SQL issue with EXISTS not "working"

I am merging 5 company records into 1 file and need to drop any

duplicates.> This is the statement:

INSERT INTO COMMON_FILE
(SELECT * FROM FILE_A A1
WHERE NOT EXISTS (SELECT 1 FROM COMMON_FILE A2 WHERE A1.ITEM =
A2.ITEM))

Problem:
The exists is not working and as a result the insert is inserting
duplicates.

What alternative ways are there to achieve this merge or what mistake
am I not seeing?

I have 5 similar SQL statements to run.

TIA

--
Darryl Freinkel

How about this?


Insert Into COMMON_FILESelectITEM, DESC, etc /* All fields need to be listed
here */From(SelectA.*,Row_Number() Over(Partition By ITEM Order By ITEM) As
ITEM_RANKFrom(Select*FromFILE_AUnionSelect*FromFILE_BUnionSelect*FromFILE_CU
nionSelect*FromFILE_DUnionSelect*FromFILE_E) As A) As BWhereITEM_RANK = 1


Jim