Discussion:
SQL issue with EXISTS not "working"
(too old to reply)
Darryl Freinkel
2014-10-23 15:56:08 UTC
Permalink
Raw Message
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Alan Shore
2014-10-23 15:59:40 UTC
Permalink
Raw Message
Try this
INSERT INTO COMMON_FILE
(SELECT distinct * FROM FILE_A)

Alan Shore
E-mail : ASHORE-***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of Darryl Freinkel
Sent: Thursday, October 23, 2014 11:56 AM
To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-23 16:47:45 UTC
Permalink
Raw Message
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-***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of Darryl Freinkel
> Sent: Thursday, October 23, 2014 11:56 AM
> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Darryl Freinkel
2014-10-23 18:20:40 UTC
Permalink
Raw Message
Let me try the union with all 5 files.

On Thu, Oct 23, 2014 at 12:47 PM, Vernon Hamberg <vhamberg-***@public.gmane.org>
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-***@public.gmane.org
>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
>> Darryl Freinkel
>> Sent: Thursday, October 23, 2014 11:56 AM
>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>> unsubscribe, or change list options,
>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-23 18:37:08 UTC
Permalink
Raw Message
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-***@public.gmane.org>
> 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-***@public.gmane.org
>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
>>> Darryl Freinkel
>>> Sent: Thursday, October 23, 2014 11:56 AM
>>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>>> unsubscribe, or change list options,
>>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>> To subscribe, unsubscribe, or change list options,
>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Darryl Freinkel
2014-10-23 18:56:57 UTC
Permalink
Raw Message
@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-***@public.gmane.org>
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-***@public.gmane.org>
>> 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-***@public.gmane.org
>>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
>>>> Darryl Freinkel
>>>> Sent: Thursday, October 23, 2014 11:56 AM
>>>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>>>> unsubscribe, or change list options,
>>>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>> To subscribe, unsubscribe, or change list options,
>>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
John R. Smith, Jr.
2014-10-23 19:07:41 UTC
Permalink
Raw Message
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] 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-***@public.gmane.org>
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-***@public.gmane.org>
>> 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-***@public.gmane.org
>>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf
>>>> Of Darryl Freinkel
>>>> Sent: Thursday, October 23, 2014 11:56 AM
>>>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To
>>>> subscribe, unsubscribe, or change list options,
>>>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To
>>> subscribe, unsubscribe, or change list options,
>>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-23 19:19:18 UTC
Permalink
Raw Message
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] 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-***@public.gmane.org>
> 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-***@public.gmane.org>
>>> 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-***@public.gmane.org
>>>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf
>>>>> Of Darryl Freinkel
>>>>> Sent: Thursday, October 23, 2014 11:56 AM
>>>>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
John R. Smith, Jr.
2014-10-23 19:31:19 UTC
Permalink
Raw Message
As long as there are not duplicates within the individual files (non unique
ITEM) I agree this will do it.

However, if they are unique, I'm curious why the original SQL did not work
which makes me think his individual files are not unique.


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
Vernon Hamberg
Sent: Thursday, October 23, 2014 3:19 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL issue with EXISTS not "working"

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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] 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-***@public.gmane.org>
> 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-***@public.gmane.org>
>>> 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-***@public.gmane.org
>>>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On
>>>>> Behalf Of Darryl Freinkel
>>>>> Sent: Thursday, October 23, 2014 11:56 AM
>>>>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Jim It
2014-10-23 19:44:38 UTC
Permalink
Raw Message
> Subject: RE: SQL issue with EXISTS not "working"> Date: Thu, 23 Oct 2014 15:31:19 -0400
>
> As long as there are not duplicates within the individual files (non unique
> ITEM) I agree this will do it.
>
> However, if they are unique, I'm curious why the original SQL did not work
> which makes me think his individual files are not unique.


If there were multiple records with the same item# in the source table which did not exist in already exist in the target table, all of those records will be written to target table.
This really needs to utilize the Row_Number OLAP function and then select only the first occurrence of the item#.
Jim


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Darryl Freinkel
2014-10-23 19:48:54 UTC
Permalink
Raw Message
There is only 1 occurrence of ITEM in each source file.

On Thu, Oct 23, 2014 at 3:44 PM, Jim It <jim_it-***@public.gmane.org> wrote:

> > Subject: RE: SQL issue with EXISTS not "working"> Date: Thu, 23 Oct 2014
> 15:31:19 -0400
> >
> > As long as there are not duplicates within the individual files (non
> unique
> > ITEM) I agree this will do it.
> >
> > However, if they are unique, I'm curious why the original SQL did not
> work
> > which makes me think his individual files are not unique.
>
>
> If there were multiple records with the same item# in the source table
> which did not exist in already exist in the target table, all of those
> records will be written to target table.
> This really needs to utilize the Row_Number OLAP function and then select
> only the first occurrence of the item#.
> Jim
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Darryl Freinkel
2014-10-23 19:44:07 UTC
Permalink
Raw Message
I tried the NOT IN as my first option and then a few times after. It still
does not work.This would have been a great solution to use after the first
file is loaded in.

It is like the unseen values in those fields are different, but they do
show up when I do a GROUP BY with count. They get counted together.

On Thu, Oct 23, 2014 at 3:19 PM, Vernon Hamberg <vhamberg-***@public.gmane.org>
wrote:

> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] 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-***@public.gmane.org
>> >
>> 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-***@public.gmane.org>
>>>> 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-***@public.gmane.org
>>>>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf
>>>>>> Of Darryl Freinkel
>>>>>> Sent: Thursday, October 23, 2014 11:56 AM
>>>>>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>>>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Darryl Freinkel
2014-10-23 19:41:02 UTC
Permalink
Raw Message
The item is unique to each file.

On Thu, Oct 23, 2014 at 3:07 PM, John R. Smith, Jr. <smith5646-***@public.gmane.org>
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] 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-***@public.gmane.org>
> 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-***@public.gmane.org>
> >> 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-***@public.gmane.org
> >>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf
> >>>> Of Darryl Freinkel
> >>>> Sent: Thursday, October 23, 2014 11:56 AM
> >>>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> >>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To
> >>>> subscribe, unsubscribe, or change list options,
> >>>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >>>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To
> >>> subscribe, unsubscribe, or change list options,
> >>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
> > unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
> unsubscribe,
> or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
John R. Smith, Jr.
2014-10-23 16:37:49 UTC
Permalink
Raw Message
Are the duplicates records that are already in common_file or are you
getting duplicates from FILE_A? If the latter, try select distinct *
from...

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
Darryl Freinkel
Sent: Thursday, October 23, 2014 11:56 AM
To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-23 16:49:40 UTC
Permalink
Raw Message
If he uses UNIONs even the duplicates in each file will be removed.

On 10/23/2014 11:37 AM, John R. Smith, Jr. wrote:
> Are the duplicates records that are already in common_file or are you
> getting duplicates from FILE_A? If the latter, try select distinct *
> from...
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
> Darryl Freinkel
> Sent: Thursday, October 23, 2014 11:56 AM
> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe,
> or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
John R. Smith, Jr.
2014-10-23 16:54:19 UTC
Permalink
Raw Message
I'm reading his SQL to be that he wants to insert into COMMON_FILE records
that are not already in COMMON_FILE which this UNION does not handle. If he
has a matching record in COMMON_FILE and FILEA, he will get a duplicate
because you are not excluding what is already there.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
Vernon Hamberg
Sent: Thursday, October 23, 2014 12:50 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL issue with EXISTS not "working"

If he uses UNIONs even the duplicates in each file will be removed.

On 10/23/2014 11:37 AM, John R. Smith, Jr. wrote:
> Are the duplicates records that are already in common_file or are you
> getting duplicates from FILE_A? If the latter, try select distinct *
> from...
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
> Darryl Freinkel
> Sent: Thursday, October 23, 2014 11:56 AM
> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-23 17:20:52 UTC
Permalink
Raw Message
Yes, this would be true if he uses each statement to add to the common
file - so yeah, has to watch out, I guess

I was reading it that the common_file was a new thing - empty - in this
case, the UNION is fine, as there is nothing to check against.

Of course, there could be a UNION of the new stuff with the old - into a
work file, then CPYF into the production one - something like that.

I do suspect that the test against duplicates is not broad enough - this
usually requires testing against the entire record - all fields.

Guess we need to hear from Darryl what he is doing, whether adding to
existing or building something brand new.

Cheers
Vern

On 10/23/2014 11:54 AM, John R. Smith, Jr. wrote:
> I'm reading his SQL to be that he wants to insert into COMMON_FILE records
> that are not already in COMMON_FILE which this UNION does not handle. If he
> has a matching record in COMMON_FILE and FILEA, he will get a duplicate
> because you are not excluding what is already there.
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
> Vernon Hamberg
> Sent: Thursday, October 23, 2014 12:50 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL issue with EXISTS not "working"
>
> If he uses UNIONs even the duplicates in each file will be removed.
>
> On 10/23/2014 11:37 AM, John R. Smith, Jr. wrote:
>> Are the duplicates records that are already in common_file or are you
>> getting duplicates from FILE_A? If the latter, try select distinct *
>> from...
>>
>> -----Original Message-----
>> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
>> Darryl Freinkel
>> Sent: Thursday, October 23, 2014 11:56 AM
>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>> unsubscribe, or change list options,
>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe,
> or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Darryl Freinkel
2014-10-23 18:18:48 UTC
Permalink
Raw Message
@Vern,
COMON_FILE is empty to start with. I have tried
-- Select 1...
-- Select ITEM ...
-- Select * ...

all with the same result.

On Thu, Oct 23, 2014 at 1:20 PM, Vernon Hamberg <vhamberg-***@public.gmane.org>
wrote:

> Yes, this would be true if he uses each statement to add to the common
> file - so yeah, has to watch out, I guess
>
> I was reading it that the common_file was a new thing - empty - in this
> case, the UNION is fine, as there is nothing to check against.
>
> Of course, there could be a UNION of the new stuff with the old - into a
> work file, then CPYF into the production one - something like that.
>
> I do suspect that the test against duplicates is not broad enough - this
> usually requires testing against the entire record - all fields.
>
> Guess we need to hear from Darryl what he is doing, whether adding to
> existing or building something brand new.
>
> Cheers
> Vern
>
>
> On 10/23/2014 11:54 AM, John R. Smith, Jr. wrote:
>
>> I'm reading his SQL to be that he wants to insert into COMMON_FILE records
>> that are not already in COMMON_FILE which this UNION does not handle. If
>> he
>> has a matching record in COMMON_FILE and FILEA, he will get a duplicate
>> because you are not excluding what is already there.
>>
>> -----Original Message-----
>> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
>> Vernon Hamberg
>> Sent: Thursday, October 23, 2014 12:50 PM
>> To: Midrange Systems Technical Discussion
>> Subject: Re: SQL issue with EXISTS not "working"
>>
>> If he uses UNIONs even the duplicates in each file will be removed.
>>
>> On 10/23/2014 11:37 AM, John R. Smith, Jr. wrote:
>>
>>> Are the duplicates records that are already in common_file or are you
>>> getting duplicates from FILE_A? If the latter, try select distinct *
>>> from...
>>>
>>> -----Original Message-----
>>> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
>>> Darryl Freinkel
>>> Sent: Thursday, October 23, 2014 11:56 AM
>>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>>> unsubscribe, or change list options,
>>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>> unsubscribe,
>> or change list options,
>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
John R. Smith, Jr.
2014-10-23 18:35:18 UTC
Permalink
Raw Message
We are missing something in what you are attempting to do and we are
guessing at answers not knowing all of the details.
1) Is ITEM the only field in COMMON_FILE?
2) Are there multiple records in FILE_A with the same ITEM value?
2a) Are the multiple records in FILE_A complete duplicates or is the data
other than the ITEM on the record different?

Coming back to your original SQL, this analyzes and builds the entire
dataset from the "select * from FILE_A..." before it inserts anything. If
you have multiple records in FILE_A with the same ITEM value and ITEM does
not already exist in COMMON_FILE, all records with that ITEM will be
inserted into COMMON_FILE.

If there are multiple records in FILE_A that have the same ITEM value and
the other data on the record is identical, you can do "select distinct *
from FILE_A..." or use the UNION as mentioned by Vern as long as you are
adding records from FILE_B.

If there are multiple records in FILE_A that have the same ITEM value but
the other data on the record is different, you can't do what you are trying
to do without something to tell it which of the ITEM records you really want
since you only want one of them.

INSERT INTO COMMON_FILE
(SELECT * FROM FILE_A A1
WHERE NOT EXISTS (SELECT 1 FROM COMMON_FILE A2 WHERE A1.ITEM =
A2.ITEM))

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
Darryl Freinkel
Sent: Thursday, October 23, 2014 2:19 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL issue with EXISTS not "working"

@Vern,
COMON_FILE is empty to start with. I have tried
-- Select 1...
-- Select ITEM ...
-- Select * ...

all with the same result.

On Thu, Oct 23, 2014 at 1:20 PM, Vernon Hamberg <vhamberg-***@public.gmane.org>
wrote:

> Yes, this would be true if he uses each statement to add to the common
> file - so yeah, has to watch out, I guess
>
> I was reading it that the common_file was a new thing - empty - in
> this case, the UNION is fine, as there is nothing to check against.
>
> Of course, there could be a UNION of the new stuff with the old - into
> a work file, then CPYF into the production one - something like that.
>
> I do suspect that the test against duplicates is not broad enough -
> this usually requires testing against the entire record - all fields.
>
> Guess we need to hear from Darryl what he is doing, whether adding to
> existing or building something brand new.
>
> Cheers
> Vern
>
>
> On 10/23/2014 11:54 AM, John R. Smith, Jr. wrote:
>
>> I'm reading his SQL to be that he wants to insert into COMMON_FILE
>> records that are not already in COMMON_FILE which this UNION does not
>> handle. If he has a matching record in COMMON_FILE and FILEA, he
>> will get a duplicate because you are not excluding what is already
>> there.
>>
>> -----Original Message-----
>> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf
>> Of Vernon Hamberg
>> Sent: Thursday, October 23, 2014 12:50 PM
>> To: Midrange Systems Technical Discussion
>> Subject: Re: SQL issue with EXISTS not "working"
>>
>> If he uses UNIONs even the duplicates in each file will be removed.
>>
>> On 10/23/2014 11:37 AM, John R. Smith, Jr. wrote:
>>
>>> Are the duplicates records that are already in common_file or are
>>> you getting duplicates from FILE_A? If the latter, try select
>>> distinct * from...
>>>
>>> -----Original Message-----
>>> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf
>>> Of Darryl Freinkel
>>> Sent: Thursday, October 23, 2014 11:56 AM
>>> To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To
>>> subscribe, unsubscribe, or change list options,
>>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To
>> subscribe, unsubscribe, or change list options,
>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Darryl Freinkel
2014-10-23 18:19:35 UTC
Permalink
Raw Message
Yes, how do I exclude the duplicates?

On Thu, Oct 23, 2014 at 12:54 PM, John R. Smith, Jr. <smith5646-***@public.gmane.org>
wrote:

> I'm reading his SQL to be that he wants to insert into COMMON_FILE records
> that are not already in COMMON_FILE which this UNION does not handle. If
> he
> has a matching record in COMMON_FILE and FILEA, he will get a duplicate
> because you are not excluding what is already there.
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
> Vernon Hamberg
> Sent: Thursday, October 23, 2014 12:50 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL issue with EXISTS not "working"
>
> If he uses UNIONs even the duplicates in each file will be removed.
>
> On 10/23/2014 11:37 AM, John R. Smith, Jr. wrote:
> > Are the duplicates records that are already in common_file or are you
> > getting duplicates from FILE_A? If the latter, try select distinct *
> > from...
> >
> > -----Original Message-----
> > From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
> > Darryl Freinkel
> > Sent: Thursday, October 23, 2014 11:56 AM
> > To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> > 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
> > unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
> unsubscribe,
> or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Sam_L
2014-10-24 01:57:35 UTC
Permalink
Raw Message
So, given:

1) COMMON_FILE is empty when you start
2) ITEM values are unique in FILE_A

Then I think all the records in FILE_A should be inserted into
COMMON_FILE. Does this happen?

How are you proving "The exists is not working and as a result the
insert is inserting duplicates"? Are you sure you are doing this
correctly?

You can check for duplicate ITEMS simply with something like this:
SELECT ITEM,COUNT(*)
FROM COMMON_FILE
GROUP BY ITEM
HAVING COUNT(*) > 1

Sam

On 10/23/2014 10:56 AM, Darryl Freinkel wrote:
> 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
>

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Loading...