How to delete DUPLICATE records from MYSQL table

Some times we need to delete duplicate rows from a table. Because there is no need for keeping duplicate records in same table. Deleting these duplicate records is very easy. It can be don using some simple SQL queries.

Here’s two easy ways to clean out that table quickly.

1) Use ALTER IGNORE on MySQL 5.1+
MySQL will allow you to create a unique index on a table with duplicate records with its IGNORE SQL extension:

 ALTER IGNORE TABLE 'SHIPMENTS' ADD UNIQUE INDEX (CART_ID, TRACKING_NUMBER)  

Duplicates will be deleted.

2) Recreate the table with GROUP BY

 execute 'CREATE TABLE shipments_deduped like shipments;'  
 execute 'INSERT shipments_deduped SELECT * FROM shipments GROUP BY cart_id, tracking_number;'  
 execute 'RENAME TABLE shipments TO shipments_with_dupes;'  
 execute 'RENAME TABLE shipments_deduped TO shipments;'  
 add_index :shipments, [:cart_id, :tracking_number], :unique => true  
 execute 'DROP TABLE shipments_with_dupes;'  

Recreating the table is much, much faster than trying to delete the records in the existing table and doesn’t lock the existing table, making your application downtime minimal.

Insurance