delete duplicate rows and need to keep one from all of them in mysql

mysql, sql, sql-delete

Solution

DELETE  a
FROM    tableA a
        LEFT JOIN
        (
            SELECT MIN(ID) ID, Name, Phone
            FROM    TableA
            GROUP   BY Name, Phone
        ) b ON  a.ID = b.ID AND
                a.NAme = b.Name AND
                a.Phone = b.Phone
WHERE   b.ID IS NULL

- SQLFiddle Demo

After you have executed the delete statement, enforce a unique constraint on the column so you cannot insert duplicate records again,

ALTER TABLE TableA ADD CONSTRAINT tb_uq UNIQUE (Name, Phone)

Problem

I want to delete duplicate rows based on two columns but need to keep 1 row all of them. Duplicate rows can be more than two rows like, ``` ID NAME PHONE -- ---- ---- 1 NIL 1234 2 NIL 1234 3 NIL 1234 4 MES 5989 ``` I want to delete any of 2 rows from above 3 and keep 1 row.

Original source

Related problems