5 Quick Ways To Delete Duplicate Records From Database Which Every Developer Must Know

You can see in the above image that the students with id 3 and 7 are missing since they are the duplicate records.

Now, I can use the Delete statement in my query to delete these records. The query is.

DELETE FROM STUDENT WHERE     
Id NOT IN(SELECT MIN(Id) AS 'Id' FROM Student GROUP BY Name, Age, Standard)

After running this query, the table duplicate records get deleted as shown in the below image.

Way 2: Using Distinct Statement

If you want to use Distinct Statement which returns Non-Duplicate rows then you will have to create a new table with an exact definition like your previous table. Then insert all ‘Non-Duplicate’ rows from the previous table to the new table.

I created a new table called Student1 and added all the distinct records to it from my previous table called ‘Student’ using the below query:

Select  Distinct * into Student1 from Student;

Now I can drop my ‘Student’ table and rename ‘Student1’ to ‘Student’. The below 2 queries do this work for me:

DROP Table Student  
EXEC sp_rename 'Student1', 'Student' 

Way 3: Using Programming Language

using (var context = new MyContext())    
{    
    var students = context.Student.GroupBy(s => new { s.Name, s.Age, s.Standard }).SelectMany(grp => grp.Skip(1)); ;    
    context.Student.RemoveRange(students);    
    context.SaveChanges();    
}    

Common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. I use CTE to with ‘PARTITION BY’ statement to delete duplicate records like this:

WITH myTemp    
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Name, Age, Standard ORDER BY Id) RN FROM Student)    
DELETE FROM myTemp WHERE RN > 1;  

Notice the Row_Number() function which provides consecutive numbering of the rows in the result by the order selected in the OVER clause. It will assign the value 1 for the first row and increase the number for the subsequent rows.

Way5: Using Self Join

Self Join which is an Inner Join on the same table can also be used to remove duplicate records. Here you create the self-join by making use of 2 things which are given below,

• In the ‘on’ condition you check the duplicate columns.
• In the ‘where’ condition you compare the ‘primary key’ columns of the tables.

So, the below Self Join query will give me the duplicate columns of the ‘Student’ table:

Select * From Student s Inner Join Student s1     
On s.Name=s1.Name And s.Age=s1.Age And s.Standard=s1.Standard     
Where s.Id>s1.Id  

So all I have to do is just put a Delete statement in order to delete the duplicate records.

Delete Student where Id in(    
Select s.Id From Student s Inner Join Student s1     
On s.Name=s1.Name And s.Age=s1.Age And s.Standard=s1.Standard     
Where s.Id>s1.Id)

Conclusion

In this tutorial, you learned 5 quick ways to delete your duplicate records from a database table. You can choose any deletion method based on your liking. Don’t forget to like this tutorial and share it with your friends.

Do read my other related database tutorial on C# Corner which is How To Restore Northwind Database In SQL Server In One Minute