How to remove duplicate(fields) entries from a mysql db?

Question

I want to remove duplicate records of table in MySQL database.

In that image id is auto increment but i want customer_invoice_id unique but want to delete duplicate record. E.G i want to delete 1104 record, but now i delete 1105 which is latest record with this query.

ALTER IGNORE TABLE table ADD UNIQUE KEY idx1(customer_invoice_id); 

Answer 1

Before you set the constraint, do manual delete first.

DELETE  a
FROM    tableName a
        LEFT JOIN
        (
            SELECT  customer_invoice_id, MAX(id) id
            FROM    tableName 
            GROUP   BY customer_invoice_id
        ) b ON  a.customer_invoice_id = b.customer_invoice_id AND
                a.id = b.id
WHERE   b.customer_invoice_id IS NULL

this will preserve the latest record for every customer_invoice_id. and you can now execute this statement,

ALTER TABLE tableName ADD UNIQUE KEY idx1(customer_invoice_id)

Demo,

• SQLFiddle Demo