---
title: "Deleting duplicate records in a MySQL database while keeping only one"
url: https://www.geekcorelab.cn/en-US/post/mysql-database-removes-duplicate-records-and-only-retains
updated: 2026-11-04T03:36:52.082319Z
author: "hhjmk"
summary: "文章介绍了如何使用SQL查询和删除数据库中重复的type数据，首先通过分组和筛选找出重复的type及其最小id，然后通过子查询确定需要删除的非最小id记录。由于MySQL不能直接删除查询结果，需借助临时表实现删除操作。最终通过删除非最小id的记录，保留每个type的最小id数据。"
---

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