Re: SQL issue with EXISTS not "working"

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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.