Delete Duplicate records

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 1

I wrote this query to show me which record are duplicates, but can't figure out how to delete the records, but leave one:

select SERVICEOPTIONSKEY, ACCOUNTKEY, count(serviceoptionskey) AS [COUNT] from billing.ACCOUNTSERVICE

where serviceoptionskey = '1003'

group by accountkey, SERVICEOPTIONSKEY

having (count(serviceoptionskey) > 1)

ORDER BY [COUNT]

Jason P. Burnett SSCommitted Points: 1759 More actions · Answer 2

Check out this function. It makes deleting dupes quite simple.

Regards,

Jason P. Burnett
Senior DBA

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Here is an example, (Which I copied, more than likely from a posting by Jeff Moden on SSC)

SELECT *

INTO #TestData

FROM (SELECT 'malli','o2o2020' UNION ALL

SELECT 'malli','878hjh8' UNION ALL

SELECT 'reddy','ghhg5' UNION ALL

SELECT 'reddy','56656' UNION ALL

SELECT 'eswa','ttt656' UNION ALL -- not duplicate

SELECT 'rama','ettt' UNION ALL -- not duplicate

SELECT 'homer','whatever' UNION ALL

SELECT 'homer','dodah' UNION ALL

SELECT 'hammer','nails' UNION ALL

SELECT 'homer','DOH!') testdata (Name, SomeValue);

GO

;with numbered as(SELECT rowno=row_number() over

(partition by Name order by Name),Name,SomeValue FROM #TestData)

select * from numbered

This results in:

rownoNameSomeValue

1eswattt656

1hammernails

1homerDOH!

2homerwhatever

3homerdodah

1mallio2o2020

2malli878hjh8

1ramaettt

1reddyghhg5

2reddy56656

When you have tested the code you can replace the Select statement with a delete statement such as:

DELETE FROM #TestData WHERE Rownumber > 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]