CODEWITHSUNDEEP
sqloracle11gduplicates
Mouzzam Hussain
Mouzzam Hussain

Reputation: 459

Deleting duplicates keeping the minimum ID

I have a persons table with duplicate person inserted with different id. I want to delete the person with duplicate names keeping only the person with the minimum ID. for e.G The record for Absalon with ID 18398 should remain and all the other duplicates are deleted.

enter image description here

Upvotes: 16

Views: 10185

Answers (3)

Alexander
Alexander

Reputation: 20224

DELETE FROM persons WHERE id NOT IN (SELECT MIN(id) FROM persons GROUP BY name)

Upvotes: 15

Ajit Jadhav
Ajit Jadhav

Reputation: 1

Solution for mysql

DELETE tb2<br>
FROM persons as tb1, persons as tb2<br>
WHERE (tb1.name=tb2.name AND tb1.id<tb2.id)

Upvotes: 0

Dilbag Singh
Dilbag Singh

Reputation: 1

WITH duplicatable As
( SELECT *, ROW_NUMBER() OVER( PARTITION BY name, ORDER BY id ASC ) as row_number FROM table_name
)
SELECT * FROM duplicatable WHERE row_number > 1

Upvotes: 0

Related Questions