Find and delete duplicates in MySQL

find-delete-duplicates.sql

-- Find Duplicates and group by duplicated value
SELECT field, COUNT(*)
  FROM mytable GROUP BY field
HAVING count(*) > 1

-- Find Duplicates and list each row
SELECT a.field, a.target_field
FROM mytable a
   INNER JOIN (
     SELECT target_field
      FROM mytable GROUP BY target_field
     HAVING COUNT(target_field) > 1) dup
    ON a.target_field = dup.target_field;

-- Delete Duplicates
DELETE t1 
  FROM mytable t1 INNER JOIN mytable t2 
WHERE
  t1.id > t2.id AND t1.field = t2.field;
  
-- Update duplicated values
UPDATE mytable t1
JOIN (
  SELECT field, COUNT(*)
  FROM mytable GROUP BY field
HAVING count(*) > 1) t2
ON t1.field = t2.field
SET t1.field = NULL;

-- Delete Duplicates (old)
DELETE
FROM table t1
WHERE EXISTS (SELECT *
  FROM   table t2
  WHERE  t1.field = t2.field
  GROUP  BY field
  HAVING COUNT(*) > 1
)