-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Unless something has changed, exists doesn't retrieve any data, so
~ where not exists (select * from GoodFile b ...) is no less efficient
than select 1.
Pete Hall
pbhall@xxxxxxxxxxxxx
Jim Essinger wrote:
| You are welcome Glenn,
| I was not sure that "Not Exists" would work. I also think that the
| subselect could also have selected "1 from GoodFile b" instead of naming
| all of the fields. The important part of the subselect is the WHERE
| statement that matches records from GoodFile with BadFile. So the
statement
| could have looked like;
|
| delete
| from BadFile a
| where not exists
| (select 1
| from GoodFile b
| where a.company = b.company
| and a.debtor = b.debtor
| and a.cc = b.cc
| and a.yy = b.yy
| and a.mm = b.mm
| and a.slsman = b.slsman )
|
| Jim
|
|
| On Tue, Jun 17, 2008 at 8:06 PM, Glenn Gundermann <ggundermann@xxxxxx>
| wrote:
|
|> Jim,
|>
|> Thank you very much for taking the time to reply and sharing your
|> knowledge!
|> This was very valuable and I can't thank you enough.
|>
|> Take care,
|>
|> Glenn Gundermann :-)
|> ggundermann@xxxxxx
|> Cell: 416-317-3144
|>
|> ----- Original Message -----
|> From: "Jim Essinger" <dilbernator@xxxxxxxxx>
|> To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
|> Sent: Tuesday, June 17, 2008 12:57 AM
|> Subject: Re: SQL: How to delete records based on another file
|>
|>
|>> Glen,
|>> I would try something like this. I don't have a system available to
test
|>> on, so test well.
|>>
|>> delete
|>> from BadFile a
|>> where not exists
|>> (select b.company, b.debtor, b.cc, b,yy, b,mm, b,slsman
|>> from GoodFile b
|>> where a.company = b.company
|>> and a.debtor = b.debtor
|>> and a.cc = b.cc
|>> and a.yy = b.yy
|>> and a.mm = b.mm
|>> and a.slsman = b.slsman )
|>>
|>> HTH
|>>
|>> Jim
|>>
|>> On Mon, Jun 16, 2008 at 9:03 PM, Glenn Gundermann <ggundermann@xxxxxx>
|>> wrote:
|>>
|>>> Hi folks,
|>>>
|>>> I have a file with some extra records I would like to delete and I am
|>>> hoping someone can help me. Here is the scenario:
|>>>
|>>> Let's say I have a file called GoodFile, and it has data like so:
|>>> company, debtor, cc, yy, mm, slsman, plus other fields
|>>> 61,1234567, 20, 08, 01, 1234
|>>> 61,1234567, 20, 08, 02, 5678
|>>> 61,1234567, 20, 08, 03, 6789
|>>>
|>>> Let's say I have another file called BadFile, and it has data like so:
|>>> company, debtor, cc, yy, mm, slsman, plus other fields
|>>> 61,1234567, 20, 08, 01, 1234
|>>> 61,1234567, 20, 08, 01, 4321
|>>> 61,1234567, 20, 08, 01, 1111
|>>> 61,1234567, 20, 08, 02, 5678
|>>> 61,1234567, 20, 08, 02, 8765
|>>> 61,1234567, 20, 08, 02, 2222
|>>> 61,1234567, 20, 08, 03, 6789
|>>>
|>>> I would like to remove the records from BadFile that don't have a match
|>>> (based on 6 fields shown) in GoodFile.
|>>>
|>>> Thanks.
|>>>
|>>> Glenn Gundermann
|>>> ggundermann@xxxxxx
|>>> New Cell #: 416-317-3144
|>>>
|>> --
|>> 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.
|>
|>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla -
http://enigmail.mozdev.org
iEYEARECAAYFAkhZz9sACgkQpcZsDl8OX6lD6QCgrqo8fduZd4uS62uMdUli/xt9
qL0An3qO1cffYxQNYOEBsgrA1ZKTWpcX
=g73k
-----END PGP SIGNATURE-----
midrange.com
