On gists

MySQL delete dupes on multiple columns

MySql

delete-duplicite.sql Raw #

# If you want to keep the row with the lowest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name



# If you want to keep the row with the highest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

MySQL delete dupes on multiple c Raw #

--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
)