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
.


--

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.

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