How to delete duplicate records from a table TIP #27

 

This tip is one of my favorite. Many times you found this problem you table records are duplicate due to various reason and you want to rid of duplicate records.

Lets understand this with an example

Suppose, I have a table which content duplicate rows. In my example I took a temporary table variable with name “@tblDuplicateCountry” .

This table has 3 columns 1) ID int type and identity column 2) Code VARCAHAR(10) type 3) Country VARCHAR(100)

Now see in below snap to make duplicate entry in this table I wrote following statement

Now let’s check duplicate entry how many duplicate records exists for a particular country.

Now if you see above snap you will find that NEPAL not having duplicate entry rest all the country having duplicate records. India has 3 duplicate records while rest of the country having 2 duplicate record.

Now our next step is to delete these duplicate records and show all the non duplicate records.

To achieve this we are using self join as shown in below figure

If you see in above figure you will find, We used a inner join with DELETE and join is based on code ,Country & Id.

I hope this tip will help you somewhere in your work.

Enjoy !!!!

Regards,

Raj