How to remove two lines that differ only in identifier

Question

I have a script that inserted twice into a database with the same data. Is there a good way to do this (without scanning, pasting each record into an array and then deleting duplicate array records)?

Answer 1

MySQL supports multi-table DELETE, which is really cool and can help here. You can make a self-join by equality of all columns except id, and then delete the corresponding row with a large one id.

DELETE t2
 FROM mytable t1 JOIN mytable t2
  USING (column1, column2, column3) -- this is an equi-join
WHERE t1.id < t2.id;

Answer 2

DELETE
FROM t
WHERE ID IN (
    SELECT MAX(ID)
    FROM t
    GROUP BY {Your Group Criteria Here}
    HAVING COUNT(*) > 1
)

Answer 3

, , . , GROUP BY.

mytable m1

( SELECT 1 FROM mytable
= m1.fields
id < m1.id )

Answer 4

, mysqldump -complete-insert, , , - , , INSERT REPLACE . PK .