Re: SQL: How to delete records based on another file

["Jim Essinger" <dilbernator@xxxxxxxxx>]

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