Handling Duplicate Records in SQL

This happens a lot (to me at least 🙂 ) so I decided to make a note here!

Following SQL script will allow you to SELECT duplicate records in a given table.

SELECT * FROM my_table WHERE some_field IN (SELECT some_field FROM my_table GROUP BY some_field HAVING COUNT(some_field) > 1)

This code actually finds all the records in a table named “my_table” which have the same value for a field (or column) named “some_field” in more than 1 records which is specified in the final clause COUNT(some_field) > 1

To delete (remove) duplicate records you only need to change “SELECT *” to “DELETE”.

DELETE FROM my_table WHERE some_field IN (SELECT some_field FROM my_table GROUP BY some_field HAVING COUNT(some_field) > 1)