Deleting Duplicate Records

D

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)
 
C

CarolK

Can you direct me to this other group? I am attempting the same thing and
have not found the answer Dustin received.
 
A

Allen Browne

CarolK said:
Can you direct me to this other group? I am attempting the same thing and
have not found the answer Dustin received.

This reply was posted to m.p.a.queries on 20th March:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top