Re: SQL issue with EXISTS not "working"

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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