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
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