How to find duplicate values in a table?
With the SQL statement below you can find duplicate values in any table, just change the tablefield into the column you want to search and change the table into the name of the table you need to search.
In your recordset you will see the tablefield and how many times it is found as a duplicate.
SELECT tablefield, COUNT(tablefield) AS dup_count
FROM table
GROUP BY tablefield
HAVING (COUNT(tablefield) > 1)
Some further tempering with the statement gets the complete records that are double. (yeah yeah.. no * should be used in the SELECT) It's just for demonstrating folks!!
SELECT *
FROM table
WHERE tablefield IN (
SELECT tablefield
FROM table
GROUP BY tablefield
HAVING (COUNT(tablefield ) > 1)
)
To go even further in the process and DELETE every double record we could do something like make a temporary table, insert the double records, delete it from the original table and insert the saved single records from the temporary table.
5074 viewed | 3 opinion(s) | del.icio.us | Digg it | Tjarko @ 01/12/05 23:56 cet