Many of the interviews I was asked a common MySQL question and the questions was, Write a MySQL query to delete the duplicated records from MySQL table.
Here in this small article I'll be explaining MySQL query to get duplicate records and to delete the duplicate records.
For example you have a users table and inside the users table we have duplicate email id of user so the first question how to get the duplicate records ?
Here is the MySQL query to get the duplicate records:
|
1
2
3
4
5
6
|
SELECT
email, COUNT(email)
FROM
users
GROUP BY email
HAVING COUNT(email) > 1;
|
It will return duplicate emails along with the count of duplicate emails.
Now we wanted to delete the duplicate records. Here are some scenarios, either you wanted to keep the highest record or you wanted to keep the lowest record.
The following MySQL query will delete the duplicate records and keeps the highest id:
|
1
|
DELETE u1 FROM users u1, users u2 WHERE u1.id < u2.id AND u1.email = u2.email
|
The following MySQL query will delete the duplicate records and keeps the lowest id:
|
1
|
DELETE u1 FROM users u1, users u2 WHERE u1.id > u2.id AND u1.email = u2.email
|
More from my site
-
Second Highest Salary without subquery in MySQL | Nth highest Salary without subquery | MySQL second highest salary query
-
How many columns we can update in single MySQL query and What is maximum length of column name, table name and database name?
-
Left join with last record of right table in Laravel & Mysql
-
Batch update in php mysql
-
Batch insert in php mysql
-
Prepared statement in php