steve Kim steve Kim - 1 month ago 17
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

info
value but keep the row with the higher
post_id
.

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 n1.info= n2.info";


Thanks!

Answer

You can do this with a join and delete:

delete t
    from table_1 t join
         (select t2.info, max(post_id) as post_id
          from table_1 t2
          group by t2.info
         ) t2
         on t.info = t.info 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.

Comments