Delete All Duplicates From Table

Question

I am trying to write a stored procedure that will delete all duplicate orders from my table. I have the below query, but if an entry exists more than 2 times I have to run the process again. How can I delete all duplicates with one run of the stored procedure?

Delete
From [Orders]
Where Id In
(
Select Max(id)
from [Orders]
Group By OrderNumber
Having Count(id) > 1
)

Answer 1

Turn the logic around, use NOT IN and MIN function for those that shouldn't be deleted

Delete
From [Orders]
Where NOT Id In
(
Select MIN(id)
from [Orders]
Group By OrderNumber
Having Count(id) > 1
)

Answer 2

If you want to delete all rows for a duplicate order number, you original query was pretty close:

Delete
From [Orders]
Where OrderNumber In
(
Select OrderNumber
from [Orders]
Group By OrderNumber
Having Count(id) > 1
)

If you want to keep one row per order number, the normal way to do it is

; WITH numbering AS (
    SELECT rowno = row_number() OVER(PARTITION BY OrderNumber ORDER BY <yourchoicehere>)
   FROM  Orders
)
DELETE numbering 
WHERE rowno > 1

Answer 3

Hi，@Mitch McConnell
Please also check this:

DELETE O   
FROM  Orders O  
      INNER JOIN (SELECT *,RANK() OVER(PARTITION BY ID ORDER BY <primary key>) rank  
                  FROM #Person) T   
      ON O.OrderNumber  = T.OrderNumber   
WHERE rank > 1;  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.