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,

 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

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


Abhith Rajan

Written byAbhith Rajan
Abhith Rajan is a software engineer by day and a full-stack developer by night. He's coding for almost a decade now. He codes 🧑‍💻, write ✍️, learn 📖 and advocate 👍.
Connect

Is this page helpful?

Related SnippetsView All

Related ArticlesView All

Related VideosView All

High Availability and SLA for Azure SQL Managed Instance

Using SQL Data Sync for Bidirectional Data Replication in SQL Server & Azure SQL DBs | Data Exposed

Build fast, scalable data system on Azure SQL Database Hyperscale | Clearent

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.