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.