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.