Skip to content
Advertisement

Delete all duplicates except first one mysql

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

Result Image

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement