8

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.

John Woo
  • 258,903
  • 69
  • 498
  • 492
Neel
  • 537
  • 3
  • 8
  • 19
  • Also the design is flawed... if NIL 1234 only should appear once you should make that the primary key or at least make a trigger that checks for the existance so that your database stays consistent – ITroubs Mar 21 '13 at 13:18

2 Answers2

18
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

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)
John Woo
  • 258,903
  • 69
  • 498
  • 492
1
DELETE
FROM Table
WHERE Table.id NOT IN  (  
    SELECT MIN(idTable) idtable
    FROM idTable
    GROUP BY name, phone)
Matt Busche
  • 14,216
  • 5
  • 36
  • 61
  • Hello Matt, I used your query in mysql like this `DELETE FROM tablename WHERE tablename.ID NOT IN ( SELECT MIN(ID) FROM tablename GROUP BY NAME, PHONE)` But i am getting below error, #1093 - You can't specify target table 'timezone' for update in FROM clause – Neel Mar 22 '13 at 03:48
  • @Neel Check the second comment: https://stackoverflow.com/a/6025387/2550529 – SepehrM Jun 14 '18 at 12:43