Re: SQL issue with EXISTS not "working"

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
.


--

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.

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