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. ...