write a query to remove duplicate rows from MySQL table?

Question

I have a mysql table with many duplicate rows as shown in the example above, I would like to remove the duplicate rows and keep one of each, how can I do that? which query shall I use? I would also like to keep my IDs incrementally ordered such as 1, 2, 3, etc. I do not want the deletion to cause my IDs to be ordered like 1, 10, 15, etc. How can I reorder my rows incrementally by ID?

Possible duplicate of [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — Michael Heil, Nov 24 '19 at 12:41
_I would also like to keep my IDs incrementally ordered such as 1, 2, 3, etc_ Is this table TOTALLY Stand Alone? No tables link into it using the `id`? — RiggsFolly, Nov 24 '19 at 13:05
What have you tried so far? Where are you stuck? Why do you want to reuse IDs? — Nico Haase, Mar 30 '22 at 12:28

score 1 · Answer 1 · answered Nov 24 '19 at 12:38

1

Here is one way:

delete t1
from mytable t1
inner join mytable t2
    on t1.id > t2.id
    and t1.parametername = t2.parametername
    and t1.parametertype = t2.parametertype
    and t1.parameterclass = t2.parameterclass
    -- more equality conditions that define the duplicates

This will delete the duplicate records as defined in the on clause of the join, while keeping the on that has the smallest id.

answered Nov 24 '19 at 12:38

GMB

216,147
25
84
135

I would still like to keep my rows ordered incrementally by ID such as 1,2, 3, etc. This solution would cause the IDs to be like 1, 5, 7, 100, etc. – Mouna Camelia Hammoudi Nov 24 '19 at 12:43
@user3406764: so what you mean is that you want to reaffect the `id`s after deleting the duplicate records? – GMB Nov 24 '19 at 12:45

score 1 · Accepted Answer · answered Nov 24 '19 at 12:57

If you want to renumber the ids, I would suggest removing the data and re-inserting it.

create table temp_t as 
    select min(id) as min_id, parametername, parametertype, . . .  -- list the columns
    from t
    group by parametername, parametertype;

truncate table t;   -- back it up first!

insert into t (parameternae, parametertype, . . .)
    select parametername, parametertype, . . . 
    from temp_t
    order by min_id;

This assumes that id is auto incrementing. If it is not, you can use:

insert into t (id, parametername, parametertype, . . .)
    select row_number() over (order by min_id), parametername, parametertype, . . . 
    from temp_t
    order by min_id;

write a query to remove duplicate rows from MySQL table?

2 Answers2