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?

Answer 1

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;

Answer 2

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.