Thursday, 16 February 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 Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts