Skip to main content

Find and delete duplicates in MySQL on multiple columns.

--Find Duplicates

SELECT t.ID, t.id_A, t.id_B
FROM (
  SELECT id_A, id_B
  FROM table_name
  GROUP BY id_A, id_B
  HAVING count(*) > 1
) x, table_name t
WHERE x.id_A = t.id_A AND x.id_B = t.id_B
ORDER BY t.id_A, t.id_B

--Delete duplicates

DELETE FROM table_name WHERE id IN (
    SELECT * FROM (
        SELECT t.ID
        FROM (
            SELECT id_A, id_B
            FROM table_name
            GROUP BY id_A, id_B
            HAVING count(*) > 1) x, table_name t
        WHERE x.id_A = t.id_A AND x.id_B = t.id_B
        ORDER BY t.id_A, t.id_B
    ) AS p
)