Pankaj  Kumar Choudhary
How will you delete duplicate data from a table in sql
By Pankaj Kumar Choudhary in SQL on Feb 23 2015
  • Ajay Vishwakarma
    Jan, 2019 16

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

    • 1
  • Tushar Dikshit
    Sep, 2017 28

    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/ -------------------------------------------------------------------------------------------------------------

    • 0
  • kamini mishra
    Jul, 2016 23

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

    • 0
  • Sanjeev Kumar
    Jul, 2016 19

    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 >1This query Remove all the duplication name from the table

    • 0
  • Umesh Maurya
    Jun, 2016 30

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

    • 0
  • Pankaj  Kumar Choudhary
    Feb, 2015 23

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

    • 0

Most Popular Job Functions


MOST LIKED QUESTIONS