CODEWITHSUNDEEP
sqlsql-servert-sql
Jacob
Jacob

Reputation: 45

Remove duplicates without key

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)?

Upvotes: 0

Views: 62

Answers (1)

Darrin Cullop
Darrin Cullop

Reputation: 1210

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.

Upvotes: 4

Related Questions