|
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.