How to delete duplicates in SQL table with Primary Key

Question

As an example, consider the following table.

    +-------+----------+----------+------------+
    | ID(PK)| ClientID | ItemType | ItemID     |
    +-------+----------+----------+------------+
    | 1     | 4        | B        | 56         |
    | 2     | 8        | B        | 54         |
    | 3     | 276      | B        | 57         |
    | 4     | 8653     | B        | 25         |
    | 5     | 3        | B        | 55         |
    | 6     | 4        | B        | 56         |
    | 7     | 4        | B        | 56         |
    | 8     | 276      | B        | 57         |
    | 9     | 8653     | B        | 25         |
    +-------+----------+----------+------------+

We have a process that's causing duplicates that we need to delete. In the example above, clients 4, 276, and 8653 should only ever have one ItemType/ItemID combination. How would I delete the extra rows that I don't need. So in this example, I'd need to delete all row contents of ID(PK)s 6, 7, 8, 9. Now this would need to happen on a much larger scale so I can't just go in one by one and delete the rows. Is there a query that will identify all ID(PK)s that aren't the lowest ID(PK) so I can delete them? I'm picturing a delete statement that operates on a subquery, but I'm open to suggestions. I've tried creating a rownumber to identify duplicates, however, because the table has a PK all rows are unique so that hasn't worked for me.

Thank you!

Edit: Here's the expected result

    +-------+----------+----------+------------+
    | ID(PK)| ClientID | ItemType | ItemID     |
    +-------+----------+----------+------------+
    | 1     | 4        | B        | 56         |
    | 2     | 8        | B        | 54         |
    | 3     | 276      | B        | 57         |
    | 4     | 8653     | B        | 25         |
    | 5     | 3        | B        | 55         |
    +-------+----------+----------+------------+

@mustaccio I'm using RazorSQL to access an Azure SQL database. — acecabana, Mar 15 '16 at 14:05

score 2 · Answer 1 · answered Mar 15 '16 at 14:02

2

You can use CTE:

;WITH ToDelete AS (
   SELECT ROW_NUMBER() OVER (PARTITION BY ClientID, ItemType, ItemID 
                             ORDER BY ID) AS rn
   FROM mytable
)
DELETE FROM ToDelete
WHERE rn > 1

answered Mar 15 '16 at 14:02

Giorgos Betsos

71,379
9
63
98

How to delete duplicates in SQL table with Primary Key

1 Answers1