Deleting duplicate records based on specific column(s) in Oracle, MySQL and MSSQL
In some cases we may need to find out duplicate records based on one or more columns of table and also we may need to delete those duplicate records by keeping only one record of the same.
There are multiple ways we can find out duplicate records and delete them ,below are few ways to perform the same
- By using Group by and max(primary/unique key) or min(primary/unique key)-delete all records which are not in max(primary/unique key) or min(primary/unique key)
- By using self join with delete
Query to find out duplicate records
For example table contacts has id,first_name,last_name,eamil,age columns and then if we need to find out contacts having duplicate email ids then we can use below query
Below sql query works well both in Oracle, MySQL, MSSQL
Table data
Oracle/MSSQL delete Query -By using Group by and min/max
if we want to retain most recent record then we need use max and if we want to keep first old record and delete the rest then we can use min
the above query will not work in MySQL- 1093 error will be thrown , in MySQL, we can’t modify the same table which you use in the SELECT part. we can rewrite the above to query with subquery to make it work in MySQL.
MySQL delete Query -By using Group by and maxmin/max
Output
Oracle/MySQL/MSSQL Delete Query -By using self join with delete
if we want to retain most recent record then use c1.id<c2.id and if we want to keep first old record and delete the rest then we can use c1.id>c2.id
Output
