Delete duplicate records from table in single statement (SQL Server)

Deleting duplicate records is very common requirement therefore multiple options are available to delete duplicate records. However this article provides the solution to delete the duplicate records using single statement.

Delete from <TABLE_NAME> where <ID> not in ( select max(<ID>) from TABLE_NAME> group by <DUPLICATE_COLUMN>)

For example:

Employee table

EmpId	EmpFullName	EmpSalary	EmpTitle
1	Sumit Bajaj	5000	Dev
2	Amit	10000	SDev
3	Sumit Bajaj	5000	Dev
4	Priyanka	50000	Mgr
5	Umesh	10000	SDev
6	Umesh	10000	SDev
7	Amit	10000	SDev
8	Geetika	5000	Dev

where few records are duplicate and need to be removed.

For this table, delete query would be 

Delete from Employee where EmpId not in (select max(EmpId) from Employee group by EmpFullName)

After executing this query you will left with only unique records as shown below.

EmpId	EmpFullName	EmpSalary	EmpTitle
3	Sumit Bajaj	5000	Dev
4	Priyanka	50000	Mgr
6	Umesh	10000	SDev
7	Amit	10000	SDev
8	Geetika	5000	Dev

Thanks for reading and hope this article helped.