Delete Duplicate Records In SQL Using Common Table Expression

In many scenarios, a developer needs to delete duplicate records from the table frequently according to their requirements.

Below are a few SQL lines of code that detect and delete duplicate records from a table in the SQL database.

Following is an example of a table with duplicate records.

2 out of 5 records, 1 & 5 are duplicates.

Now let’s delete those duplicate records using SQL queries.

Let’s assume that the table name is [UserRole], so the query for deleting the duplicate records will be as below.

WITH tblTempUserRole as    
(    
SELECT ROW_NUMBER() Over(PARTITION BY [UserName], [RoleName] ORDER BY UserName)    
   As RowNumber, * FROM [UserRole]
)    
DELETE FROM tblTempUserRole WHERE RowNumber >1    
  
SELECT * FROM [UserRole]

after executing the query, the above query will delete all duplicate records. then run a select query to get the records and we will get only 4 records.

Below are the records after execution.