Deleting Duplicates in the same table.

[Robs]

Hi

Iw ould like to know how to delete duplicates in the same table, using a
query, as opposed to importing the data again, and declaring a primary key.

How would I go about doing this?

Thanks

 

[John Spencer]

Is there a way that the duplicates differ? In other words, is there a primary
key on the table?

If you can't pick one record of the duplicates out from another, then I know
of no good method to delete all but one of the duplicated records other than
the methods outlined below.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

One way to handle this would be to build a new table with a compound unique
index based on up to 10 fields. Then import all the records into this new
table and ignore the errors. Once the import is successful, delete the old
table and rename the new table to the old table's name.

Another way, (BACK UP your data before you do this)
Build a query based on the table that will identify the primary key values you
want to keep and save that as qKeepThese. If you don't care which of the
"duplicate" records you want to keep then you can use the First aggregate
function to more-or-less randomly select one.

Query One: << This query is the key to identifying which records to keep>>
SELECT First(PrimaryKeyField) as FirstID
FROM TheTable
GROUP BY TelephoneNumber
(Or by multiple fields)

Query Two:
DELETE DistinctRow T.*
FROM TheTable as T
WHERE T.PrimaryKeyField IN
(SELECT PrimaryKeyField
FROM TheTable LEFT JOIN QKeepThese
ON TheTable.PrimaryKeyField= QKeepThese.FirstID
WHERE qKeepThese.FirstID is Null)

All in one query would be as follows - only works if field and table names
don't contain "Special" characters.

DELETE DistinctRow T.*
FROM TheTable as T
WHERE T.PrimaryKeyField IN
(SELECT PrimaryKeyField
FROM TheTable LEFT JOIN
(SELECT First(PrimaryKeyField) as FirstID
FROM TheTable
GROUP BY TelephoneNumber) AS QKeepThese
ON TheTable.PrimaryKeyField= QKeepThese.FirstID
WHERE qKeepThese.FirstID is Null)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County