Remove duplicates without key

Question

I'm trying to remove all duplicates in one of my tables. None of them are keys or unique. Tried to google some solutions but they don't seem to work.

delete T1
from MyTable T1, MyTable T2
where T1.MyTableCustomer = T2.MyTableCustomer
and T1.MyTableCustomerId = T2.MyTableCustomerId
and T1.MyTableSoldToParty = T2.MyTableSoldToParty

So I want to remove all rows where there already exists an equal row with the exact same value on all of the columns.

How can I archive this in SQL Server (2017)?

Edit the question add some sample data & desired result would helpful. — Yogesh Sharma, Mar 13 '19 at 17:19
Possible duplicate of [How to delete duplicate rows in sql server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) — Eric Brandt, Mar 13 '19 at 17:20

score 4 · Answer 1 · answered Mar 13 '19 at 17:36

This is tricky because without any IDs or unique keys, it is hard to specify which record to delete. However, SQL Server does offer a way to pull it off. I found a solution on this page and adapted it to your table. Try this:

WITH CTE AS
(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY MyTableCustomer, MyTableCustomerId, MyTableSoldToParty ORDER BY MyTableCustomer, MyTableCustomerId, MyTableSoldToParty) AS RN
    FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

It works by assigning an additional column that is basically a counter for all identical records, and then deletes the records that have a value greater than 1. This will preserve ONE of the duplicate values and remove all of the others.

Remove duplicates without key

1 Answers1