Sabyasachi Mishra
How to delete duplicate rows in a Table keeping only one using MSSQL Query?
By Sabyasachi Mishra in SQL Server on Jul 23 2015
  • Sabyasachi Mishra
    Jul, 2015 23

    WITH TempId AS (SELECT *, row_number() OVER(PARTITION BY ID, FNAME,LNAME ORDER BY ID) AS [Num]FROM Employee)DELETE TempId WHERE [Num] > 1Select * from EmployeeStill you can find the details in SQLFiddle herehttp://sqlfiddle.com/#!6/394a9/1

    • 3
  • Joe Wilson
    Dec, 2015 24

    Delete from table_name where field name not in (Select max(field name) from table_name group by field name)

    • 2
  • Hrishikesh Chaturvedi
    Sep, 2015 22

    Delete from table where id not in (Select max(id) from table group by id)This is the simple query to delete duplicate records from the table.

    • 1
  • Suraj Kumar
    Sep, 2015 9

    SELECT DISTINCT * INTO NewTable From OriginalTable TRUNCATE TABLE OriginalTable INSERT INTO OriginalTable SELECT * FROM NewTable DROP TABLE NewTable

    • 1
  • Lan Huu
    Feb, 2017 1

    Delete from table_name where field name not in (Select max(field name) from table_name group by field name) => incorrect T-SQL E.g: Items tbl has data as IdK IdP 1 1 1 2 2 1 2 1

    • 0
  • Vineet Kumar
    Jan, 2017 13

    by using row_number with seperated by argument and then deltete where you get row no. more than 1

    • 0
  • Umesh Maurya
    Jun, 2016 30

    delete t1 from Table t1 , t2 where t1.Namecol=t2.NameCol and t1.Id>t2.Id

    • 0
  • sriramanadh sriramanadh
    Jan, 2016 6

    -- deleting the duplicate rows form the table using CETwith cet_deupdelete as(select * ,ranking = DENSE_RANK() over (partition by empno,ename,job,mgr,hiredate,sal,comm,deptno order by newid() asc) from empdup)select * from cet_deupdelete where ranking >1

    • 0
  • Kaustubh
    Dec, 2015 26

    Copy Paste this code and Run In a new SQL query Window > > CREATE TABLE [dbo].[Employee]( [ID] INT NOT NULL, [Email] [varchar](50) NOT NULL ); --drop table dbo.Employee INSERT INTO [dbo].[Employee] VALUES (1,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (2,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (3,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (4,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (5,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (6,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (7,'[email protected]'); --SELECT MAX(ID),EMAIL,ROW_NUMBER() OVER (ORDER BY EMAIL ASC) AS 'ROW' --FROM DBO.EMPLOYEE --GROUP BY EMAIL WITH T1 AS ( SELECT ID FROM DBO.EMPLOYEE EXCEPT SELECT MAX(ID) AS ID FROM DBO.EMPLOYEE GROUP BY Email HAVING COUNT(Email) > 1 ) DELETE FROM dbo.Employee WHERE ID IN (SELECT ID FROM T1) SELECT * FROM DBO.EMPLOYEE

    • 0
  • Sujeet Suman
    Sep, 2015 2

    Please once go through below link. http://www.dotnet-tricks.com/Tutorial/sqlserver/IL3S290812-Remove-duplicate-records-from-a-table-in-SQL-Server.html

    • 0
  • Pankaj  Kumar Choudhary
    Aug, 2015 26

    WITH TempEmp (Emp_Name,Emp_Age,Emp_Salary,Emp_City,Dupli_Col) AS ( SELECT *,ROW_NUMBER() OVER(PARTITION by Emp_Name, Emp_Age,Emp_Salary,Emp_City ORDER BY Emp_Name) AS Dupli_Col FROM Employee_Detail )/* DELETE Duplicate Data */ Delete from TempEmp where Dupli_Col>1/* Select Query */

    • 0
  • Pankaj  Kumar Choudhary
    Aug, 2015 26

    WITH TempEmp (Emp_Name,Emp_Age,Emp_Salary,Emp_City,Dupli_Col) AS ( SELECT *,ROW_NUMBER() OVER(PARTITION by Emp_Name, Emp_Age,Emp_Salary,Emp_City ORDER BY Emp_Name) AS Dupli_Col FROM Employee_Detail )

    • 0
  • Devanand Laroiya
    Aug, 2015 13

    --- First create the table and insert duplicate values CREATE TABLE dbo.tblEmployee (ID INT IDENTITY, EmpID INT, Name VARCHAR(50)) INSERT INTO dbo.tblEmployee VALUES (1,'Name1') INSERT INTO dbo.tblEmployee VALUES (2,'Name2') INSERT INTO dbo.tblEmployee VALUES (1,'Name1') INSERT INTO dbo.tblEmployee VALUES (2,'Name2') INSERT INTO dbo.tblEmployee VALUES (3,'Name3') INSERT INTO dbo.tblEmployee VALUES (3,'Name3') --- Its the Quickest way to delete the Duplicetes --- The Below SQL Query will first fetch the nonunique records and then the Delete those DELETE FROM dbo.tblEmployee WHERE ID IN (SELECT ID FROM dbo.tblEmployee e LEFT OUTER JOIN (SELECT MAX(ID) AS DupID, EmpID FROM dbo.tblEmployee GROUP BY EmpID) eUnique ON e.ID = eUnique.DupID WHERE eUnique.DupID IS NULL)

    • 0
  • Sanjeev Kumar
    Aug, 2015 9

    CREATE TABLE san(ID INT)---Add duplicate IDs--- execute below command to remove duplicate ; WITH tmp AS ( SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Id) AS row FROM san ) DELETE FROM tmp WHERE row!=1

    • 0


Most Popular Job Functions


MOST LIKED QUESTIONS