Remove-if duplicates 10 or more!

  • Thread starter Thread starter Old Warrior
  • Start date Start date
O

Old Warrior

I am trying to remove records from a database if there are 10 or more
duplicates. For example, let's say I check the 'Name' field, if a
particular name shows up 10 or more times, I want all those records
removed. Can someone help me with creating a Query to do this, or at
least point me in the right direction?
 
One approach would be to create a "unique values" query (that's a property
you can set for the query). Then create a new table with the exact
structure the old table (you can copy/paste structure only). Finally,
append the unique values query results to the new table.

By the way, I don't understand why you would want to keep 9 duplicate names
but not 10?
 
Sql may have to be tweaked:
DELETE FROM MyTable
WHERE MyCol in
(SELECT MyCol from MyTable group my MyCol having count(*) > 9
 
This is basically a duplicates query such as the query wizard will create with
one slight modification in the having clause.

SELECT UniqueID, SomeNames
FROM SomeTable
WHERE SomeNames IN
(SELECT T.SomeNames
FROM SomeTable as T
GROUP BY T.SomeNames
HAVING Count(SomeNames) > 9)

You can turn this into a delete query
DELETE SomeTable.*
FROM SomeTable
WHERE SomeNames IN
(SELECT T.SomeNames
FROM SomeTable as T
GROUP BY T.SomeNames
HAVING Count(SomeNames) > 9)

This should delete ALL records where there are more than 9 multiples. It will
not leave ANY of the matches.

Test this on a COPY of your table to make sure you get the desired results.
 
Back
Top