Post
Ask Question

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.

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