Delete Duplicate Rows in MySQL

Published: 12 years ago

Over time tables can contain duplicate entries, one of the main reasons is the system inserting the data is either not preparing it correctly, or not checking against existing records.

The Statement

Before you run the query make sure you backup the table as it will remove data, an extra check you could always do a SELECT query instead of DELETE.

DELETE FROM table1
USING table1, table1 AS vtable
WHERE vtable.id > table1.id
AND table1.field_name = vtable.field_name