Find / Update / Delete Duplicated Rows in a table with Columns

if you need to update / delete / get rows that contains duplicated rows, this uses a CTE table to get the data and then either DELETE / UPDATE or SELECT these columns.


 ; WITH CTE AS 
(
    SELECT rn = ROW_NUMBER() 
                OVER( 
                  PARTITION BY [duplicatedColumn01], [duplicatedColumn02]
                  ORDER BY [duplicatedColumn01] desc), * 
   FROM [dbo].[TableWithDuplicates] cp
  
) 

/*******************************************************************
DELETE 
  FROM cte 
 WHERE  rn > 1
******************************************************************/

/*******************************************************************
UPDATE cte
   SET [Column01] = 0 
  FROM cte 
 WHERE  rn > 1
*******************************************************************/

SELECT *
  FROM cte 
 WHERE  rn > 1