MySQL delete duplicate rows but keep one

As you are working in the Mysql database. And you have to delete duplicate data from your MySQL database, but you have to keep one latest row record or row. For this, there are many methods, in which you can create queries for that in MySQL database.

In this tutorial, you will learn how to find and delete duplicate records while keeping the latest records in a MySQL database table. You’ll learn how to find and remove duplicate rows but keep one latest.

If you are finding MySQL query to find duplicate rows or records 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 deletes duplicate rows but keep one latest.

Find and Delete duplicate Records MySQL

By using the following methods, you can find and delete all duplicate rows except for one in MySQL or MySQL deletes duplicate rows but keep one latest:

• Method 1: Find duplicate rows
• Method 2: Find duplicate records
• Method 3: MySQL deletes duplicate rows but keep one latest

Method 1: Find duplicate rows

In this first way, you will learn how you can find 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 user’s 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                   |
 +---------------------+---------------------+

Method 2: Find duplicate records

In the second method, you will learn the easy way to find 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 user’s 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                   | [email protected]      |
 +---------------------+---------------------+
 | 3                   | [email protected]      |
 +---------------------+---------------------+
 | 4                   | [email protected]       |
 +---------------------+---------------------+ 
 | 5                   | [email protected]       |
 +---------------------+---------------------+

Method 3: MySQL deletes duplicate rows but keep one latest

Let’s take an example of how you can keep one row and delete the remaining rows in the MySQL database table. So use the below query for MySQL to 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

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

Recommended MySQL Tutorials

If you have any questions or thoughts to share, use the comment form below to reach us.