steve Kim steve Kim - 1 year ago 84
MySQL Question

mysql deleting duplicate rows

I have a db table (table_1) that had duplicate values but different autoincremented id:

enter image description here

I want to delete rows that are duplicated with the

value but keep the row with the higher

I tried the following but wasn't getting any results (maybe the db is too big, although it only has ~20,000 rows). I just want to run it by your guys and see if I am doing it right.

$query = "DELETE n1 FROM table_1 n1, table_1 n2 WHERE n1.post_id < n2.post_id AND";


Answer Source

You can do this with a join and delete:

delete t
    from table_1 t join
         (select, max(post_id) as post_id
          from table_1 t2
          group by
         ) t2
         on = and t.post_id < t2.post_id;

If you have a large table and a significant proportion of the rows need to be deleted, then it is often faster to write a query to keep the rows you want to keep, truncate the table, and re-insert the rows.