Reducing Duplicates in a Table

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 2005 Forums

Transact-SQL (2005)

Reducing Duplicates in a Table

Author

Topic

mcrors_calhoun
Starting Member

22 Posts

Posted - 2007-05-24 : 10:46:38

Hi,

I have a table full of contractors, but there are alot of duplicates in this table. I would like to get rid of the duplicates so that I am left with just a clean list of contractors. Here is an example

contracter Address
ACCELERATED CLEAN COMPLEX BLUEBELL IND ESTATE DUBLIN 12
ACCELERATED CLEANING LTD COMPLEX BLUEBELL IND ESTATE DUBLIN 12

I want to get rid of one of these. These entries will have different contractor id's.

I am unsure of where to even start solving this problem, could someone please help.

Thanks

McRors

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-24 : 10:49:44

This article may help you:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-05-24 : 10:51:26

Do you have the postal code in the seperate column that you could do a group by count on

eg

select companyname, count(postalcode)
from contractors
group by postalcode
having count(postalcode) >1

or something along these lines anyway

svicky9
Posting Yak Master

232 Posts

Posted - 2007-05-24 : 12:54:00

The Best idea would be to use a join for comparing the 2 fields .

a. create an srn field like srn numeric(6) identity(1,1) and a dupe field like dupe varchar(100)
b. If the field ur comparing is called field1

then execute the following query first

select
a.field1
,b.field1
from
table a
join
table b
on a.field1 = b.field1
where
a.srn > b.srn

You will get the list of all duplicates in the table.Once you are satisfied with that Update the table like this

update
a
set
a.dupe = 'yes'
from
table a
join
table b
on a.field1 = b.field1
where
a.srn > b.srn

------------------
and then keep tightening the query using substrings.

select
a.field1
,b.field1
from
table a
join
table b
on substring(a.field1,1,15) = substring(b.field1,1,15)
where
a.srn > b.srn

and then use the update query...

Hope this helps.

Vic

http://vicdba.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources