Deduping mySQL table

Question

A programming error lead to multiple insertions of identical rows in this table. I am aware that constraints in the schema could prevent the insertions.

I am trying to find a way to delete all but the newest (highest id) row for each round/number pair. I could definitely script this but I wondered if there is a way to do this in pure SQL?

Answer 1

Generally you can do:

delete from your_table
where id not in 
(
    select max(id) from your_table
    group by user, round, date, number
)

In MySQL you can't delete from the same table you are selecting from. But you can trick MySQL with another subquery like this:

delete from your_table
where id not in 
(
   select * from 
   (
      select max(id) from your_table
      group by user, round, date, number
   ) x
)