Remove all duplicates except one

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)

Remove all duplicates except one

Author

Topic

Rauken
Posting Yak Master

108 Posts

Posted - 2005-08-31 : 19:18:56

My client has duplicates in his database and I need to remove the duplicates except for one record.

Example:
One table called place. For each place there are serviceobjects connected. Place can be Mc Donald's, serviceobjects might be one ore more fire extinguishers. Now they registered two serviceobjects of the same kind and want to remove one and keep one. It's only one kind of serviceobjects that they managed to get duplicates on which seems to make it easier because I might be able to count?

It doesn't matter which duplicate that is removed.

Here's a short description of the tables.

place:
placeId int

serviceobject:
serviceobjectId int
placeId int
serviceobjectTypeId int

Constraints, indexes? the creators of the db didn't see the strength in that.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-31 : 19:47:15

In our FAQ we have an article about deleting duplicates. See if that helps you.

---------------------------
EmeraldCityDomains.com

Rauken
Posting Yak Master

108 Posts

Posted - 2005-08-31 : 19:48:54

I solved it. Maybe not in the nicest way.. cursor.
I ran a cursor on Place looping all placeId's

for each placeId I implemented a while(select count(serviceobjectid) where serviceobjecttypeid = 161 and placeId = @placeId) > 1
then delete and break.

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-01 : 00:06:11

Did you look at the FAQ? There's a cursorless method outlined there.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 01:46:41

The thing that can be done with cursor can also be done without cursor with more efficient way

Refer here also as nigel suggested many methods
http://sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Madhivanan

Failing to plan is Planning to fail

Rauken
Posting Yak Master

108 Posts

Posted - 2005-09-01 : 09:54:08

Thanks for the help. My script didn't actually work. When I found a group of records with duplicates, I deleted them all. Fortunately I had a script already lined up to create new serviceobject for all the places that didn't have any.

I'll remember the duplicate faq until next time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources