Delete Duplicate Rows

  • How to delete duplicate rows in a table?

    I had no Primary Key and Identity Column ON TABLE

    empid ename

    101 A

    102 A

    103 A

    104 B

    104 B

  • satish.thota2002 (8/4/2008)


    How to delete duplicate rows in a table?

    I had no Primary Key and Identity Column ON TABLE

    empid ename

    101 A

    102 A

    103 A

    104 B

    104 B

    One solution is to select DISTINCT * from this table into another table, then move the data back/rename the new table while getting rid of the original.

    Another solution on SQL Server 2005 is to use a delete statement with row_number()

    DELETE a

    FROM ( SELECT *

    , row_number() OVER ( PARTITION BY empid, ename ORDER BY empid ) AS rowNr

    FROM myTable) a

    WHERE rowNr > 1

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • I think there are thousands of replies to a question like this in the forums.

    The point is: why no primary key? Is there any good reason not to add a primary key to a table? I think there isn't.

    Anyway, use a temp table to get rid of duplicates:

    select *, cnt = row_number() over (partition by candidateKey, order by candidateKey)

    into #tempTable

    from sourceTable as a

    where candidateKey in (

    select candidateKey

    from sourceTable

    group by candidateKey

    having count(*) > 1

    )

    --delete duplicates

    delete from sourceTable

    where candidateKey in (

    select candidateKey

    from #tempTable

    )

    insert into sourceTable

    select * -- (I mean: all fields except the one you computed with row_count)

    from #tempTable

    where cnt = 1

    This should work or at least give you some piece of advice, but please, use primary keys!;)

    -- Gianluca Sartori

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

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