Delete Duplicate Rows in SQL Server.

Deleting duplicate rows in SQL Server

--1.Deleting rows from a table having primary key

CREATE TABLE TEST1
( ID INT NOT NULL PRIMARY KEY
,VALUE CHAR(2)
)

INSERT INTO dbo.TEST1 SELECT '1','A' UNION ALL SELECT '2','A' UNION ALL SELECT '3','A' UNION ALL SELECT '4','B' UNION ALL SELECT '5','B' UNION ALL SELECT '6','B'

The original table with duplicates

DELETE FROM dbo.TEST1 WHERE ID NOT IN (SELECT MIN(ID) FROM dbo.TEST1 GROUP BY VALUE)

Now the resulting table with no duplicate rows

2. Deleting rows from a table without a primary key

I have a table dbo.MYDATABASE with following data---


now deleting the duplicate rows using ROW_NUMBER()


WITH TEMP_TABLE AS ( SELECT ROW_NUMBER() OVER( PARTITION BY CUSTID ORDER BY CustID ) AS ROWNUMBER,* FROM dbo.MYDATABASE )

DELETE FROM TEMP_TABLE WHERE ROWNUMBER > 1


The result looks like