Clay Shannon wrote:

> Anybody have a SQL example for deleting duplicate records in a table
> (deleting all but one)?
>
>
>
> Or better yet, one that deletes redundant records that have certain column
> values in common, regardless of the values in all other columns? E.g., if
> Client_ID, firstname, middlename, lastname are the same, regardless of what
> may be in address, city, state, zip, etc. fields, delete the dupes?
>

You can try something like:

delete from redundant_table deltable
where deltable.ID in (
select seltable.ID from redundant_table seltable
where (seltable.ID <> deltable.ID) and
(seltable.Client_ID = deltable.Client_ID) and
etc.
);

You have to make sure that your table has a unique identifier per record
(called ID in the example) otherwise you don't have a reference to the
current record.

--
With kind regards,
Intersoft

Bart Bakker
Senior Software Engineer
--
e: bart@...
t: +31 (0)20 581 19 75
f: +31 (0)20 581 19 70
w: http://www.intersoftsoftware.com/
This correspondence and every attachment is for the named person's use
only. It may contain confidential or legally privileged information, or
both. No confidentiality or privilege is waived or lost by any
mis-transmission. If you receive this correspondence in error, please
immediately delete it from your system and notify the sender. You must
not disclose, copy or rely on any part of this correspondence if you are
not the intended recipient. Intersoft and/or its employees are not
responsible for the incorrect or incomplete transmission of this email
or any attachments, nor for any delays in receipt. Any views expressed
in this message are those of the individual sender, except where the
sender expressly, and with authority, states them to be the views of
Intersoft.