MYSQL Delete FIRST duplicates from table

Question

I have 511 duplicates in my table, I need to delete these, but not the originals. Therefore I want to delete every second occurrence of every duplicate.

How can I do this?

TABLE:

code  /   status  /   time
E3F4FF928A  /  0 /
07D95444BB  /  0 /  
07D95444BB /   0 / 
40006C128F  / 0  / 1315293012
2B45790E52   /  0  /
40006C128F  / 0  / 1315293012

Answer 1

add a unique index to the table on the column that should be unique, and ignore errors

alter ignore table X add unique index (column_a) 

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Answer 2

Just run into this problem today (no unique key in the table).

I solved it like this

DELETE FROM table where code=x and status=y and time=z LIMIT 1;

This will delete the first 1 row for the given criteria (if you have n duplicates, put n-1 to keep only one).