Find and delete duplicate records in MySQL; In this tutorial, we will learn how to find duplicate records or rows in the database table. And as well as, will learn how to find and delete duplicate rows but keep ones into MySQL database table.

The find duplicate rows tutorial explains to you 3 easy query to find duplicate records or rows in your database table. And also delete duplicate rows keep one.

If you are finding MySQL query for find duplicate rows or record and delete duplicate records or data from the MySQL database table. This tutorial will help you to find and delete duplicate rows but keep one without a primary key, delete duplicate records in MySQL except one, delete all duplicate rows except for one in MySQL, MySQL delete duplicate rows keep latest.

How to Find and Delete duplicate records in MySQL

Use the following methods to find and delete duplicate records in MySQL; as follows:

• The first way – Find duplicate rows
• The second way – Find duplicate records
• MySQL delete duplicate rows but keep one

The first way Find duplicate rows

In this first way, we will learn how you can find the duplicate records with the count in your database table.

Let’s take an example, let’s have one table name users and where we will check duplicate rows using the email column in the users table. So we can use the below MySQL query to find duplicate rows in your database table with the count.

SELECT
    id,
    COUNT(email)
FROM
    users
GROUP BY email
HAVING COUNT(email) > 1;

Output – Find duplicate rows

The above query output looks like this:

+---------------------+---------------------+
| id                  | Count               |
+---------------------+---------------------+
| 2                   | 5                   |
+---------------------+---------------------+
| 4                   | 3                   |
+---------------------+---------------------+

The second way – Find duplicate records

In the second way, we will learn second easy way to find the duplicate records in your database table.

Let’s take the second example, let’s have one table name users and where we will check duplicate rows using the email column in the users table. So we can use the below MySQL query to find duplicate rows in your database table with the count.

SELECT id, email
FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING count(email) > 1
    )
ORDER BY email

Output – Find duplicate records

The above query output looks like this:

+---------------------+---------------------+
| id                  | Email               |
+---------------------+---------------------+
| 2                   | test@gmail.com      |
+---------------------+---------------------+
| 3                   | test@gmail.com      |
+---------------------+---------------------+
| 4                   | way@gmail.com       |
+---------------------+---------------------+ 
| 5                   | way@gmail.com       |
+---------------------+---------------------+

MySQL delete duplicate rows but keep one

Let’s take an example of how you can keep one row and delete remaining rows in MySQL database table. So use the below query for MySQL delete duplicate rows but keep one:

delete users
   from users
    inner join (
     select max(id) as lastId, email
       from users
      group by email
     having count(*) > 1) duplic on duplic.email = users.email
  where users.id < duplic.lastId;

The above query output is look like this:

Conclusion

In this MySQL tutorial, you have learned how you can find duplicate rows or records in your database table. And also you have learned how you can keep one row and delete remaining rows in MySQL database table.