Thursday, February 16, 2017

How to Delete Duplicate records or rows from a Table in SQL Server

SQL Server ROW_NUMBER() Function with Common Table Expressions (CTEs) to Delete the Duplicate records or rows from a Table

WITH MyCTE AS
       (
         Select* From 
            SELECT *, ROW_NUMBER()OVER(Partition by EMP_ID 
                 Order by Dept_Name) Rankx FROM Tbl_DupData 
              ) S1
         WHERE Rankx >
        )
Delete FROM MyCTE
SELECT *FROM Tbl_DupData
     ;
GO

Example:
Let us Suppose we have a Table with Duplicate records as follows:
Select*from Tbl_DupData

Now we have to identify the duplicate records using the ROW_NUMBER() function on above table as follows :
SELECT *, ROW_NUMBER() OVER(Partition By EMP_ID Order By Dept_Name) Rankx FROM Tbl_DupData


If you want to Select only the Duplicate records from above table, we can do as follows:
Select * From 
    (Select *, ROW_NUMBER() OVER(Partition By EMP_ID Order By Dept_Name) Rankx                 FROM Tbl_DupData 
    ) S1 WHERE S1.Rankx >1


To delete these Duplicate records from the Table, we have to use the Common Table Expression method as follows :

WITH MyCTE AS
       (
         Select* From SELECT *, ROW_NUMBER() OVER(Partition By EMP_ID 
         Order By Dept_Name) Rankx FROM Tbl_DupData 
                              ) S1
         WHERE Rankx >
        )
Delete FROM MyCTE
SELECT *FROM Tbl_DupData
     ;
GO

Final Output without duplicates :

-------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog