Delete all duplicates except first one mysql

Question

I have a table with a column serial_number that is repeated a few times. How would I delete the entire row except the first duplicate? By the following, I can select all the duplicates. But can't delete.

SELECT serial_number, COUNT(*) FROM trademark_merge GROUP BY serial_number HAVING COUNT(*) > 1

Answer 1

Assuming that the primary key of your table is id, you could phrase this as a delete/join query, like:

delete tm
from trademark_merge tm
inner join (
    select serial_number, min(id) id
    from trademark_merge 
    group by serial_number 
) tm1 on tm.serial_number = tm1.serial_number and tm.id > tm1.id