Tuesday, August 10, 2010

How to Remove Duplicate Records from a Table, Considering a Subset of Columns - Sql

I wanted to delete some duplicate records from a table. "Duplicate" doesn't mean identical records in this scenario. I wanted to check whether the values in a subset of columns are identical and if so, to remove additional records keeping just one record in the table. 

Let's assume we have a table called Person like below

IdNameAgeCity
10Sunil24Matara
11Sandun25Colombo
12Nimali23Matara
13Dilani25Matara
14Savini25Galle

Say we need only one person from one city. Then we have to remove two records having Matara as the city.

To do that we can write a query like below

DELETE FROM Person WHERE Id > (SELECT MIN(Id) FROM Person p where p.City = Person.City)

We used Id as a controller to decide which records to delete and to keep just one record (by checking min Id).

After executing the query, the table will look like this


IdNameAgeCity
10Sunil24Matara
11Sandun25Colombo
14Savini25Galle

2 comments:

Maria Owens said...

We can learn here how o remove duplicate record from a table and we can see the writer discussed it very nicely and clearly anyway we can see this post is helping us very much in superiorpapers.com review that really deserve thanks and appreciation.

asim jofa said...

We must should read this post to know easily about how to remove duplicate record from a table. I will share this information with trust essay writing service also to make a very beneficent flow of knowledge easily.

Related Posts with Thumbnails