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.