Deleting Duplicate Records

[dberger16]

Hi,

I've got my duplicates records query and now I want to delete these 1100
dups in my table of 12,000+ records.

Can you please explain how I can easily delete these duplicate records in my
table now that I have found them in my query.

Thanks very much.

Dave
(e-mail address removed)

 

[Allen Browne]

Use a subquery to identify which duplicate records to delete, and which one
to keep.

This example assumes:
- a table named Table1;
- a primary key named ID;
- "duplicate" is defined as same Company, Address, and City;
- you want to keep the lowest ID value.

DELETE FROM Table1
WHERE ID <>
(SELECT Min(ID) AS MinOfID
FROM Table1 AS Dupe
WHERE Dupe.Company = Table1.Company
AND Dupe.Address = Table1.Address
AND Dupe.City = Table1.City);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html#DeleteUnmatched

 

[Dale Fye]

This is generally quite a challenge, because you may have data in one record
that is not in the other. In Allen's example, he assumes you want to keep
the earliest record (one with the lowest ID value), but what if one record
has a Fax # and the other doesn't? Which one do you delete then.

Generally, when I have this situation, I run a duplicates query to identify
the offending records, then just browse the records to see which one I want
to delete. This gives me the added flexibilty of modifying data in one
record with data from the other if it is missing from the one I want to keep.
This can be time consuming, and you can write code that loops through the
records and checks for those fields which might vary between the records, but
it is just time consuming. The alternative is to use a technique similar to
Allen's and just make an assumption about which one to keep.

HTH
Dale