Re: Complicated delete query

From: Brian Camire (bcamire_at_hotmail.com)
Date: 04/07/04 

Date: Wed, 7 Apr 2004 13:54:32 -0400

How about something like this:

DELETE
FROM Alphadex
WHERE
NOT
((Nz(First & Last, "") <> "" OR Nz(Company, "") <> "")
AND
(Nz(Address1 & Address2 & City & State & Zip, "") <> "" OR Nz(Phone, "") <>
""))

"Ragnar Midtskogen" <ragnar_ng@optonline.net> wrote in message
news:%23x6OY$LHEHA.3992@TK2MSFTNGP10.phx.gbl...
> Hello,
>
> I have an application with a table used as an address book.
> The table has fields for name, address, phone and company name.
>
> Over the years the client has entered many incomplete records so he asked
if
> I would clean up the table.
> What should be left in the table are records which have at least:
> name and address
> name and phone
> company name and address
> company name and phone
>
> I think what I need to do is to select for deletion those records which
only
> has name or address or phone or company name, but the query below is the
> only one I can come up with. It works, but it just seems needlessly
> complicated.
>
> DELETE *.*
> FROM Alphadex
> WHERE (
> (
> (((Alphadex.Last) Is Null) Or ((Alphadex.Last)=""))
> AND
> (((Alphadex.Address1) Is Null) Or ((Alphadex.Address1)=""))
> AND
> (((Alphadex.Address2) Is Null) Or ((Alphadex.Address2)=""))
> AND
> (((Alphadex.City) Is Null) Or ((Alphadex.City)=""))
> AND
> (((Alphadex.State) Is Null) Or ((Alphadex.State)=""))
> AND
> (((Alphadex.Zip) Is Null) Or ((Alphadex.Zip)=""))
> AND
> (((Alphadex.Phone) Is Null) Or ((Alphadex.Phone)=""))
> AND
> (((Alphadex.Company) Is Null) Or ((Alphadex.Company)=""))
> )OR
> (
> (((Alphadex.First) Is Null) Or ((Alphadex.First)=""))
> AND
> (((Alphadex.Address1) Is Null) Or ((Alphadex.Address1)=""))
> AND
> (((Alphadex.Address2) Is Null) Or ((Alphadex.Address2)=""))
> AND
> (((Alphadex.City) Is Null) Or ((Alphadex.City)=""))
> AND
> (((Alphadex.State) Is Null) Or ((Alphadex.State)=""))
> AND
> (((Alphadex.Zip) Is Null) Or ((Alphadex.Zip)=""))
> AND
> (((Alphadex.Phone) Is Null) Or ((Alphadex.Phone)=""))
> AND
> (((Alphadex.Company) Is Null) Or ((Alphadex.Company)=""))
> )
> OR
> (
> (((Alphadex.First) Is Null) Or ((Alphadex.First)=""))
> AND
> (((Alphadex.Last) Is Null) Or ((Alphadex.Last)=""))
> AND
> (((Alphadex.Phone) Is Null) Or ((Alphadex.Phone)=""))
> AND
> (((Alphadex.Company) Is Null) Or ((Alphadex.Company)=""))
> )
> OR
> (
> (((Alphadex.First) Is Null) Or ((Alphadex.First)=""))
> AND
> (((Alphadex.Last) Is Null) Or ((Alphadex.Last)=""))
> AND
> (((Alphadex.Address1) Is Null) Or ((Alphadex.Address1)=""))
> AND
> (((Alphadex.Address2) Is Null) Or ((Alphadex.Address2)=""))
> AND
> (((Alphadex.City) Is Null) Or ((Alphadex.City)=""))
> AND
> (((Alphadex.State) Is Null) Or ((Alphadex.State)=""))
> AND
> (((Alphadex.Zip) Is Null) Or ((Alphadex.Zip)=""))
> AND
> (((Alphadex.Company) Is Null) Or ((Alphadex.Company)=""))
> )
> OR
> (
> (((Alphadex.First) Is Null) Or ((Alphadex.First)=""))
> AND
> (((Alphadex.Last) Is Null) Or ((Alphadex.Last)=""))
> AND
> (((Alphadex.Address1) Is Null) Or ((Alphadex.Address1)=""))
> AND
> (((Alphadex.Address2) Is Null) Or ((Alphadex.Address2)=""))
> AND
> (((Alphadex.City) Is Null) Or ((Alphadex.City)=""))
> AND
> (((Alphadex.State) Is Null) Or ((Alphadex.State)=""))
> AND
> (((Alphadex.Zip) Is Null) Or ((Alphadex.Zip)=""))
> AND
> (((Alphadex.Phone) Is Null) Or ((Alphadex.Phone)=""))
> )
> );
>
> BTW, note that all fields allow zero length string, so I can not just
check
> for Null.
>
> Any help would be appreciated.
>
> Ragnar
>
>

Quantcast