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.

create a sample table

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

Select Query

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

duplicate records

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

  1. WITH CTE ( ID , NAME, DuplicateCount)
  2. AS
  3. (
  4. SELECT ID , NAME,
  5. ROW_NUMBER() OVER(PARTITION BY ID , NAME ORDER BY ID) AS DuplicateCount
  6. FROM EMPLOYE
  7. )
  8. DELETE
  9. FROM CTE
  10. WHERE DuplicateCount > 1
  11. GO
run

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

run