Thursday, 16 February 2017

SQL Server Row_Number function with CTE to Delete the Duplicates from a Table

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

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 >1 )
Delete FROM MyCTE
SELECT *FROM Tbl_DupData
;
GO

Final Output without duplicates :



No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts