MySQL Delete Duplicate Rows but keep one

Find and delete duplicate records into MySQL database; In this tutorial, i am going to show you how to find and delete duplicate records or rows in the database table but keep ones into MySQL database table.

Find and Delete duplicate records in MySQL

Use the following methods to find and delete duplicate records or data in mysql except one; as follows:

• Solution 1 – Find duplicate rows
• Solution 2 – Find duplicate records
• Solution 3 – MySQL delete duplicate rows but keep one

Solution 1 – Find duplicate rows

Let’s take an example to find the duplicate records with the count in your database table; as follows:

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

Solution 2 – Find duplicate records

Let’s take an second example using find the duplicate records in your database table; as follows:

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

Solution 3 – 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;

Conclusion

MySQL find and delete duplicate records 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.

Recommended MySQL Tutorials