Find duplicate type
SELECT type FROM table GROUP BY type HAVING count(type) > 1;
Find the smallest id in duplicate type data
SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1;
Find non-smallest ids in duplicate type data (those to be deleted)
SELECT id FROM table WHERE type in( SELECT type FROM table GROUP BY type HAVING count(type) > 1) AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1);
In Mysql, records returned by a query cannot be deleted directly; this must be solved using a temporary table
SELECT id from ( SELECT id FROM table WHERE type in( SELECT type FROM table GROUP BY type HAVING count(type) > 1) AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1) ) as t;
Delete duplicate type data (keep only one, the smallest id)
DELETE FROM table WHERE id IN (
SELECT id from (
SELECT id FROM table WHERE type in(
SELECT type FROM table GROUP BY type HAVING count(type) > 1)
AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1)
) as t
);