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

Answer 2

You can just delete using a LEFT JOIN to find duplicates with a higher id;

DELETE i1 
FROM invoices i1
LEFT JOIN invoices i2
  ON i1.customer_id = i2.customer_id 
 AND i1.customer_invoice_id = i2.customer_invoice_id
 AND i1.id < i2.id
WHERE i2.customer_invoice_id IS NOT NULL

An SQLfiddle to test with.

As always, back up your data before running updates/deletes from random people on the Internet :)