sql-server

SQL Server - Delete Duplicate Rows

by Abhith RajanDecember 04, 2019 · 2 min read · Last Updated:
Share this

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,

1WITH CTE AS(
2 SELECT [Col1]
3 ,[Col2]
4 ,...
5 ,ROW_NUMBER() OVER(PARTITION BY [Col1] ORDER BY [Col1]) AS RN
6 FROM [dbo].[Table]
7)
8SELECT * 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.

1WITH CTE AS(
2 SELECT [Col1]
3 ,[Col2]
4 ,...
5 ,ROW_NUMBER() OVER(PARTITION BY [Col1] ORDER BY [Col1]) AS RN
6 FROM [dbo].[Table]
7)
8DELETE FROM CTE WHERE RN > 1

See Also

This page is open source. Noticed a typo? Or something unclear?
Improve this page on GitHub

Abhith Rajan

Written by Abhith Rajan Buy me a coffee
Abhith Rajan is an aspiring software engineer with more than 6 years of experience and proven successful track record of delivering technology-based products and services.

Related Posts

Related Services

SmarterASP.net - Unlimited ASP.NET Web Hosting

ASP.NET Hosting by SmarterASP.net. Unlimited ASP.NET Hosting Plans Starting at $2.95 a month.