SQL Server - Delete Duplicate Rows

Deleting rows from a SQL Server table is no big deal. But it becomes a little complicated when we are in a situation like a table with no primary keys or unique column and it has duplicate records which we need to delete.

When tried to delete rows using the context menu in the Microsoft SQL Server Management Studio, it failed with an error message indicating the action affects multiple records. But if you try Azure Data Studio to do the same, it will delete all the similar rows, not just the one you choose to delete.

So, in this case, we can use a query like below,

 WITH CTE AS(
   SELECT [Col1]
      ,[Col2]
      ,...
      ,ROW_NUMBER() OVER(PARTITION BY [Col1] ORDER BY [Col1]) AS RN
   FROM [dbo].[Table]
)
SELECT * FROM CTE

In the above query, we are using a CTE to list the data along with an additional column RN which will have a value higher than 1 for the duplicate entries.

It is always a good idea to “SELECT” and double-check the data before doing a hard “DELETE”

So first run the above query and inspect the rows with RN>1, Once you are okay with the results, execute the delete.

 WITH CTE AS(
   SELECT [Col1]
      ,[Col2]
      ,...
      ,ROW_NUMBER() OVER(PARTITION BY [Col1] ORDER BY [Col1]) AS RN
   FROM [dbo].[Table]
)
DELETE FROM CTE WHERE RN > 1

See Also#

• WITH common_table_expression (Transact-SQL) - SQL Server