Work@Microsoft    Study@UW.edu    Live@Seattle

SQL Script to Delete Duplicated Rows in a Table

SQL Script to Delete Duplicated Rows in a Table
5 (100%) 1 vote

The following script leverages Common Table Expression (CTE) to remove duplicated rows in a table [TableName]; based on the columns [Column1Name], [Column2Name];

WITH CTE AS(
   SELECT RN = ROW_NUMBER()OVER(PARTITION BY [Column1Name], [Column2Name]; ORDER BY (SELECT 0))
   FROM [TableName];
)
DELETE FROM CTE WHERE RN > 1

 

For example, imagine a table like this, and I want to delete duplicated rows just based on col1.

WITH CTE AS(
   SELECT RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY (SELECT 0))
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

 


Leave a Comment

Your email address will not be published. Required fields are marked *

Loading...
ScottGe.net