sql-server

SQL Server - Delete Duplicate Rows

Delete duplicate records from a SQL server table without primary keys or any unique columns.

Abhith Rajan
Abhith RajanDecember 04, 2019 · 2 min read · Last Updated:

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

Abhith Rajan

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

Was this helpful?

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

Related ArticlesView All

Related VideosView All

SQL Server 2017 Graph Database

Azure SQL Game Changers for Developers | BDL112

Related StoriesView All

Related Tools & ServicesView All

sqlfum.pt

sequel fumpt

sqlfmt is an online SQL formatter. It is pronounced sequel fumpt. Its purpose is to beautifully format SQL statements.
SmarterASP.NET

SmarterASP.net - Unlimited ASP.NET Web Hosting

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