How will you delete duplicate data from a table in sql

DELETE SSI_Items WHERE BillNO NOT IN (SELECT MIN(BillNO) FROM SSI_Items GROUP BY IGroup);

There are multiple options to perform this operation. Using row count to restrict delete only 1 record set rowcount 1 DELETE FROM EMPLOYEE WHERE EMPID IN ( SELECT EMPID FROM EMPLOYEE GROUP BY EMPID,EMPNAME, SALARY HAVING COUNT(*)>1 ) set rowcount 0 -======================-======================= Use auto increment primary key "add" if not available in the table, as in given example. alter table employee add empidpk int identity (1,1) Now, perform query on min of auto pk id, group by duplicate check columns - this will give you latest duplicate records select * from employee where empidpk not in ( select min(empidpk) from employee group by EMPID,EMPNAME, SALARY ) Now, delete. Delete from employee where empidpk not in ( select min(empidpk) from employee group by EMPID,EMPNAME, SALARY ) -------------------------------------------------------------------------------------------------------------- From article --- http://www.c-sharpcorner.com/article/most-asked-sql-queries-in-interview-questions/ -------------------------------------------------------------------------------------------------------------

DELETE FROM EmployeeTable WHERE ID NOT IN ( SELECT MAX(ID) FROM EmployeeTable GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3

WIth CTE(Name,RowNumber) as ( select Name,Row_number() over(PARTITION by Name order by Name) as RowNumber from Tablename ) delete from CTE where RowNumber >1 This query Remove all the duplication name from the table

Delete t1 tableName t1,tableName t2 where t1.Name=t2.Name and t1.id>t2.id

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