Deleting duplicates keeping the minimum ID

Question

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

possible duplicate of [Removing duplicate rows from table in Oracle](http://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle) — dani herrera, Jan 15 '14 at 09:55

score 15 · Answer 1 · edited Sep 28 '22 at 14:49

15

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

edited Sep 28 '22 at 14:49

Diego Montania

322
5
12

answered Jan 15 '14 at 09:52

Alexander

19,906
19
75
162

I get an error with mysql Ver 14.14 Distrib 5.5.52: `mysql> delete from domain_map where id not in (select min(id) from domain_map group by domain); ERROR 1093 (HY000): You can't specify target table 'domain_map' for update in FROM clause` – abatie Nov 15 '17 at 22:24
@abatie Please make a new question tagged with `mysql`, since this question was not about mysql and every SQL dialect is different. – Alexander Nov 16 '17 at 01:59
@abatie Or you may want to look at https://stackoverflow.com/questions/6471463/how-to-delete-duplicates-in-sql-table-based-on-multiple-fields – Alexander Nov 16 '17 at 02:01
I was not asking a question - I was pointing out that the answer doesn't work, at the very least not in this particular version of mysql. – abatie Nov 16 '17 at 07:13

score 0 · Answer 2 · edited Jun 29 '20 at 13:31

0

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

edited Jun 29 '20 at 13:31

David Buck

3,752
35
31
35

answered Jun 29 '20 at 13:26

Dilbag Singh

1

score 0 · Answer 3 · edited Aug 15 '22 at 16:20

0

Solution for mysql

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

edited Aug 15 '22 at 16:20

buddemat

4,552
14
29
49

answered Aug 10 '22 at 20:50

Ajit Jadhav

1
1

Deleting duplicates keeping the minimum ID

3 Answers3

Solution for mysql