We have moved to www.dataGenX.net, Keep Learning with us.

Wednesday, December 26, 2012

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!!

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.

till then...
njoy the simplicity.......