Post reply

Delete Duplicate records

  • August 23, 2010 at 3:57 pm

    #226495

    Ok, I know that I'm going to kick myself when I see the answer to this, but it's Monday and I'm not thinking right. I have one table, which has several duplicate records. I want to delete anything that is a duplicate, but keep at least one of the records. Any suggestions?

  • August 23, 2010 at 3:59 pm

    #1210724

    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]

  • August 23, 2010 at 4:11 pm

    #1210726

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

    Regards,

    Jason P. Burnett
    Senior DBA

  • August 23, 2010 at 6:19 pm

    #1210742

    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:

    rowno Name SomeValue

    1 eswa ttt656

    1 hammer nails

    1 homer DOH!

    2 homer whatever

    3 homer dodah

    1 malli o2o2020

    2 malli 878hjh8

    1 rama ettt

    1 reddy ghhg5

    2 reddy 56656

    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]

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

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