Delete duplicated rows MySQL

Question

We want to delete duplicated rows on our MySQL database, and we have tried a lot of queries, but for unfortunately we haven't succeeded yet. We found this query on several posts, but didn't work either:

DELETE t1 FROM Raw_Validated_backup AS t1 INNER JOIN Raw_Validated_backup AS t2 
    ON t1.time_start=t2.time_start 
    AND t1.time_end=t2.time_end 
    AND t1.first_temp_lpn=t2.first_temp_lpn 
    AND t1.first_WL=t2.first_WL 
    AND t1.first_temp_lpn_validated=t2.first_temp_lpn_validated 
    AND t1.second_temp_lpn=t2.second_temp_lpn 
    AND t1.second_WL=t2.second_WL 
    AND t1.second_temp_lpn_validated=t2.second_temp_lpn_validated 
    AND t1.third_temp_lpn=t2.third_temp_lpn 
    AND t1.third_WL=t2.third_WL 
    AND t1.third_temp_lpn_validated=t2.third_temp_lpn_validated 
    AND t1.first_temp_rising=t2.first_temp_rising 
    AND t1.first_WR=t2.first_WR 
    AND t1.first_temp_rising_validated=t2.first_temp_rising_validated 
    AND t1.second_temp_rising=t2.second_temp_rising 
    AND t1.second_WR=t2.second_WR 
    AND t1.second_temp_rising_validated=t2.second_temp_rising_validated 
    AND t1.third_temp_rising=t2.third_temp_rising 
    AND t1.third_WR=t2.third_WR 
    AND t1.third_temp_rising_validated=t2.third_temp_rising_validated 
    AND t1.id<t2.id;

Message we receive after running query: No errors, 0 rows affected, taking 40,4 s

Incidentally, if operationally possible, it's often far quicker to create a new table, retaining just the rows you want to keep, and then dropping/archiving the old table and renaming the new one. — Strawberry
– Strawberry, Commented Jan 6, 2020 at 11:49

forpas · Accepted Answer · 2020-01-06 11:58:48Z

2

This query:

select max(id) id
from Raw_Validated_backup
group by <list of all the columns except id>

returns all the ids for the rows that you want to keep.
So delete the rest:

delete from Raw_Validated_backup
where id not in (
  select t.id from (
    select max(id) id
    from Raw_Validated_backup
    group by <list of all the columns except id>
  ) t
)

See the demo.
Another option with a self join:

delete v1 
from Raw_Validated_backup v1 inner join Raw_Validated_backup v2
on v1.time_start = v2.time_start and v1.time_end = v2.time_end and .......
and v1.id < v2.id;

See a simplified demo.

edited Jan 6, 2020 at 11:58

answered Jan 6, 2020 at 11:53

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Akina Over a year ago

Use USING (fieldslist) instead of ON - it is shorter and more clear.

Buster3650 Over a year ago

Just tested and worked as wished. Thank you very much!

forpas Over a year ago

@Akina I agree using should be handy in this case but then the last condition v1.id < v2.id should be moved to a WHERE clause.

Akina Over a year ago

the last condition v1.id < v2.id should be moved to a WHERE clause Of course.

Collectives™ on Stack Overflow

Delete duplicated rows MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related