0

i have this table structure:

+----+-------+
| ID | phone |
+----+-------+
|  3 | 000   |
|  4 | 111   |
|  5 | 111   |
|  8 | 222   |
|  9 | 333   |
+----+-------+

i know, that i can show duplicate rows with this sql:

SELECT * FROM TablePhones GROUP BY phone having count(*) > 1

but i need a sql command which deletes all duplicates but keep that row, which has the highest ID.

can you help me please? :)

Stack108
  • 915
  • 2
  • 14
  • 35

1 Answers1

0

You can do this by getting the Max ID from the duplicates and and then removing records that don't match. Here's an example:

DELETE FROM TablePhones TP
INNER JOIN (
  SELECT MAX(id) id, T.phone
  FROM TablePhones T
  INNER JOIN (
    SELECT phone
    FROM TablePhones
    GROUP BY phone
    HAVING COUNT(1) > 1
  ) Subq
  ON T.phone = Subq.phone
) SS
ON TP.phone = SS.phone
  AND TP.ID <> SS.ID

Here, the Subq Select gathers the phone numbers that are duplicate, the SS subquery then gets the max ID for these records. The final delete will select records that match the phone, but the ID doesn't match the MAX ID

Tah
  • 1,526
  • 14
  • 22
  • with this code i get this error: #1064 - Syntax Error by `'TP INNER JOIN ( SELECT MAX(id) id, T.phone FROM TablePhones T INNER JOI' in line 1` – Stack108 May 21 '16 at 19:37