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
– Yogesh Sharma, Commented Mar 13, 2019 at 17:19
Possible duplicate of How to delete duplicate rows in sql server? — Eric Brandt
– Eric Brandt, Commented Mar 13, 2019 at 17:20

Darrin Cullop · Accepted Answer · 2019-03-13 17:36:41Z

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.

Collectives™ on Stack Overflow

Remove duplicates without key

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related