To Find Duplicate Rows using Common Table Expressions and perform DML operations

Handling the Duplicates using CTE and Row_Number()  - (ie, To Delete or Update the Duplicates from SQL server Tables Using CTE, Row_Number() ) 

            Just to select the duplicate rows from a table, we can use the following kind of simple query. The below mentioned query will return duplicated rows based on the Product_Id :

                     

SELECT Product_id,

      COUNT(Product_id) AS NumOccurrences

FROM InventoryDetail

      GROUP BY Product_id

      HAVING ( COUNT(Product_id) > 1 )

If you want to Delete or Update the duplicate, we can use the following sample which will use the CTE (Common Table Expression) , Row_Number function and the MDL statement (Delete/ Update).

            

                      

WITH CTE (Product_id,Time_Id, DuplicateCount)

AS

(    

      SELECT Product_id,Time_Id,

                  ROW_NUMBER() OVER(PARTITION BY Product_id,Time_Id ORDER BY Product_id) AS DuplicateCount

      FROM InventoryDetail

)

DELETE

      FROM CTE

WHERE DuplicateCount > 1