RE: SQL issue with EXISTS not "working"

As long as there are not duplicates within the individual files (non unique
ITEM) I agree this will do it.

However, if they are unique, I'm curious why the original SQL did not work
which makes me think his individual files are not unique.


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Vernon Hamberg
Sent: Thursday, October 23, 2014 3:19 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL issue with EXISTS not "working"

I'm with John, now that we know a bit more - and I think I'd prefer to use
NOT IN instead of NOT EXISTS -

INSERT INTO COMMON_FILE (SELECT * FROM FILE_A WHERE ITEM NOT IN (SELECT ITEM
FROM COMMON_FILE))

If you have more than 1 field that must be unique, you can do something like
this, IIRC -

INSERT INTO COMMON_FILE (SELECT * FROM FILE_A WHERE (ITEM, NUTHERFLD) NOT IN
(SELECT ITEM, NUTHERFLD FROM COMMON_FILE))

I don't think you need the aliases for the files this way, either.

HTH
Vern

On 10/23/2014 2:07 PM, John R. Smith, Jr. wrote:

This is going to get tricky because neither UNION or DISTINCT will
work because the entire record is not a duplicate.

One more point to clarify...

Are there duplicate ITEM values in a single file or is ITEM unique to
each file and just duplicated across multiple files? If it is unique
by file, I think your original SQL should have worked.


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Darryl Freinkel
Sent: Thursday, October 23, 2014 2:57 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL issue with EXISTS not "working"

@John
There are 5 files, with the same fields. We are merging our companies
together from 5 to 1 and we need to get rid of the duplicates across
the companies.Each file is identical. The file has more than 100
fields and a combined total of 1.6 million records. The item number is
sometimes common across the 5 companies. The item number and
description is the same but the other fields may have different
values. The differences are irrelevant at this time.


On Thu, Oct 23, 2014 at 2:37 PM, Vernon Hamberg
<vhamberg@xxxxxxxxxxxxxxx>
wrote:

Good luck!!


On 10/23/2014 1:20 PM, Darryl Freinkel wrote:

Let me try the union with all 5 files.

On Thu, Oct 23, 2014 at 12:47 PM, Vernon Hamberg <
vhamberg@xxxxxxxxxxxxxxx>
wrote:

I fear that won't help with the 5 other queries - so I suggest a
SELECT

with UNION SELECTs.

Darryl, you must be able to make all 6 SELECTs look the same,
right, same columns, with same data types and all?

Then you could use

SELECT * FROM FILE_A1 A1
UNION
SELECT * FROM FILE_A2 A2
UNION
SELECT * FROM FILE_A3 A3
UNION
SELECT * FROM FILE_A4 A4
UNION
SELECT * FROM FILE_A5 A5
UNION
SELECT * FROM FILE_A6 A6

The duplicates will be left out - here's the documentation on that
-

"If UNION is specified without the ALL option, the result is the
set of all rows in either R1 or R2, with duplicate rows eliminated."

Your individual SELECTs may look different, if they come from
differently laid out tables, but they must already be built to put
stuff into your final table now. So just UNION them all together.

HTH
Vern


On 10/23/2014 10:59 AM, Alan Shore wrote:

Try this

INSERT INTO COMMON_FILE
(SELECT distinct * FROM FILE_A)

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On
Behalf Of Darryl Freinkel
Sent: Thursday, October 23, 2014 11:56 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL issue with EXISTS not "working"

I need some help on this one.

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

--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.