Deleting duplicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Deleting duplicate records

Author

Topic

sagavb
Starting Member

18 Posts

Posted - 2009-05-29 : 08:52:42

Hi Friends,

I hava a table which has some records ( and some of them are duplicate entries). I just want to delete all duplicate records from the table.

Can this be done by Sql query?

Table name: test

name age
----------
Mr.X 20
Mr.Y 21
Mr.X 20
Mr.Z 30
Mr.Z 30

So, the expected output is,
name age
--------
Mr.X 20
Mr.Y 21
Mr.Z 30

Regards
Saga

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 08:58:41

what's the primary key of this table ?

KH
[spoiler]Time is always against us[/spoiler]

sagavb
Starting Member

18 Posts

Posted - 2009-05-29 : 09:05:37

Unfortunately, it does not have any primary key. If it has, it wont allow for duplicate entries.

But, still is this possible (without primary key)?

Regards
Saga

quote:
Originally posted by khtan

what's the primary key of this table ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 09:13:02

[code]
alter table test add id int identity(1,1)

delete t
from test t
inner join
(
select name, age, id = min(id)
from test
group by name, age
) d on t.name = d.name and t.age = d.age and t.id <> d.id

alter table test drop column id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sagavb
Starting Member

18 Posts

Posted - 2009-05-29 : 09:27:10

Hey, Thats great la.

Thanks

quote:

Originally posted by khtan


alter table test add id int identity(1,1)

delete t
from   test t
       inner join
       (
           select name, age, id = min(id)
           from   test
           group by name, age
       ) d on t.name = d.name and t.age = d.age and t.id <> d.id

alter table test drop column id

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources