Delete duplicate rows and save one of them in mysql

Question

I want to remove duplicate rows based on two columns, but you need to save one row of them.

Duplicate lines may contain more than two lines, such as

ID NAME PHONE -- ---- ---- 1 NIL 1234 2 NIL 1234 3 NIL 1234 4 MES 5989 

I want to delete either of the two lines on top of 3 and save 1 line.

Answer 1

 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 execute the delete statement, enforce a unique constraint on the column so that you cannot reinsert duplicate records,

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

Answer 2

 DELETE FROM Table WHERE Table.id NOT IN ( SELECT MIN(idTable) idtable FROM idTable GROUP BY name, phone)