Select Duplicate Records from Table in Single Go

Introduction

Sometime we enter duplicate records in table by mistake. When we want to delete duplicate records, first we copy distinct record in a temp table then delete all records and reinsert records inthe original table. It is a time-consuming task. Today I am going to show you how to delete records in one statement.

Step1: First create a sample table named Employee with some duplicate records.



Step 2: With the help of Select Query we can see a total of nine records in our table.



Step 3: With the Help of Having clause we can find there are two duplicate records in our table.



Step 4: Withthe help of CTE we can delete all duplicate records in a single go.

WITH CTE ( ID , NAME, DuplicateCount)
AS
(
   SELECT ID , NAME,
   ROW_NUMBER() OVER(PARTITION BY ID , NAME ORDER BY ID) AS DuplicateCount
   FROM EMPLOYE
)
DELETE
   FROM CTE
WHERE DuplicateCount > 1
GO

Step 5: Withthe help of Select Query we can seethe  result we want.