Introduction
Sometimes, some duplicate data may be inserted in the SQL table. But it's harmful for you and your application too. So how do you remove the duplicate rows in your table.
Description
Take a look at the following table content. It has duplicate rows.
Id | Name | Department |
1 | Arka | .NET |
2 | Ashok | .NET |
3 | Anubhav | php |
4 | Arka | .NET |
5 | Sucheta | Graphics |
Row 1 and 4 are duplicates. So we have to remove one row and keep one in the table.
Now the question is how to do this?
Using the Code
To solve this problem, take a look at the following code. This will solve this problem.
WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1
After running, this code shows the table data, it will ensure that all unique rows are present.