Search code examples
mysqlsqlduplicatessql-insertsql-delete

write a query to remove duplicate rows from MySQL table?

enter image description here

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?

Solution

  • 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;