How to delete duplicate rows based on single column value in MSSQL?
How to delete duplicate rows based on single column value in MSSQL?
Ask by Ashutosh Patel
Updated 01 Mar 2025
All three viable approaches to delete duplicate rows in single-column values within MS SQL Server involve using Common Table Expressions (CTE) with
ROW_NUMBER()or the combination of theDELETEquery with a subquery and grouping byHAVING.The CTE (Common Table Expression) together with
ROW_NUMBER()forms an effective method for this task. You need to start by adding row numbers to each duplicate record according to the prominent duplicated field. The query retains the row with the minimum assigned number and then deletes all other duplicate rows.You can implement the
DELETEcommand with a subquery to locate duplicates through a comparison of a unique row identifier. Through this approach, the initial duplicate records remain untouched but extra duplicate entries will be automatically removed.You can employ
GROUP BYwithHAVINGclauses to recognize duplicate records while letting you remove them according to minimum or maximum ID value conditions. This technique lets you select particular duplicates that you will remove manually instead of depending on automatic row numbering.A preview of duplicate rows must be executed using
SELECTbefore performing any delete operation. The protection of data loss requires taking a backup of the database and table before any operation.