SQL: Remove duplicates

To check if your database has duplicates, run these two queries and compare the result.

select count(*) from Table

and

select distinct *  from Table

If these numbers are different, duplicate records are present. While there is no specific query to remove duplicates, you can copy all distinct values in to a new table, using:

select distinct * into NewTable from Table

 Alternatively, you can delete records where a single value is duplicated using this query. This requires a unique field, such as an ID, and will remove the record with the highest unique value.

delete T1 from MyTable T1, MyTable T2 where T1.dupField = T2.dupField and T1.uniqueField > T2.uniqueField

Last updated on 08 July 2010, at 16:46.