Deleting duplicate records in a MySQL database while keeping only one

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

Article repost policy

Request authorization before reposting and retain the authorization code, original URL, and go/affiliate links.

Original URL: https://www.geekcorelab.cn/en-US/post/mysql-database-removes-duplicate-records-and-only-retains