Delete Duplicate Records, Except 1

[eerich]

Hi Everyone,

I'm trying to remove duplicate records from a table using one of the queries found in many posts here, but I can't seem to get it to work. I'd like to keep one record of the duplicate set and discard others. Most have just 2 total duplicates, but there are a few with 3 duplicates. The code I attempted was:

Delete *
From PV
WHERE Role & ObjectID NOT IN
(SELECT max(Role & ObjectID) 
 FROM PV
 GROUP BY Role, ObjectID
 HAVING count(Role & ObjectID) > 1);

If I run the sub-select in isolation it produces the records with duplicates, so I can verify; however when running the entire query it does not delete any of the duplicates.

Any help is appreciated.

 

[dhookom]

Typically any SQL that contains a GROUP BY will not be updateable or allow deletes. You could possibly use DLookup() in place of the subquery but it would be slow if there are lots of records.

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

In the other threads posted in the forum on this query, others have noted success with it. I can't get it to work so I think I may be missing something in my sql.

 

[dhookom]

'other threads posted in the forum' can you provide an example?

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

Here are a few of the threads:
thread701-1013971 (see PHV's post)
faq701-5721
thread701-1526542

Again, while I was able to isolate my subquery to show the duplicate records, I cant exactly get it to delete all of the duplicates EXCEPT for one record.

 

[dhookom]

I believe PH's example used a single field primary key. Do you have a primary key? If not can you add one?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

see PHV's post
Your SQL is far away from my suggestion ...
DELETE FROM PV
WHERE yourPrimaryKey Not In (SELECT Max(yourPrimaryKey) FROM PV GROUP BY Role, ObjectID)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eerich]

PHV - thanks.

I don't have a primary key defined so I was deriving it from the Role and ObjectID concatenated together. Will that not work?

 

[dhookom]

eerich,
Why not simply add a primary key. IMO, don't leave home without one.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What are the fields that define duplicate records ?
What are the fields that are different in duplicate records ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eerich]

Duplicates are 'Role' and 'ObjectID'.

There are many other fields that are different such as security code, description, option code.

Thanks

 

[PHV]

I'd try something like this:
DELETE FROM PV
WHERE [security code] & description & [option code] Not In (SELECT Max([security code] & description & [option code]) FROM PV GROUP BY Role, ObjectID)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eerich]

Doesn't look i will get this to work because I have 19 fields in a single record and it won't allow me to usse any other combinations. I would've thought that if I concatenated the two fields that make up the duplicate item it would allow me to do it, but that obviously does not work.

 

[dhookom]

Again, why not add a primary key, autonumber field. Seems simple to me.

Duane
Hook'D on Access
MS Access MVP