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 2008 Forums
 Transact-SQL (2008)
 Remove Duplicates

Author  Topic 

Anouar
Starting Member

8 Posts

Posted - 2013-03-04 : 10:11:04
Hi all,

I want to remove some duplicates from a table.

The structure of the table is as follows:

ID, Contact, School

And the duplicates are listed as follows:

1 Bob 200
2 Bob 200
3 Jim 10
4 John 30
5 Wang 40
6 Wang 40

Any help would be highly appreciated

Best Regards
Anouar

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-04 : 10:30:44
Assuming that (Contact, School) constitute your logical key then this should work. You can run the SELECT first and if you like the results then run the DELETE.

select t.*
--delete t
from (
select contact, school, min(id) idToKeep
from <table>
group by contact, school
having count(*) > 1
) dupes
inner join <table> t
on t.contact = dupes.contact
and t.school = dupes.school
where t.id > dupes.idToKeep


Once that is done then you should put a UNIQUE constraint on the table to prevent more duplicate values.


Be One with the Optimizer
TG
Go to Top of Page

Anouar
Starting Member

8 Posts

Posted - 2013-03-05 : 06:55:19
Thank you that works like a charm.

Best Regards
Anouar
Go to Top of Page
   

- Advertisement -