Delete duplicate rows from the MySQL table

Sometime our database may contain most of the duplicate data which shows redundant data on the frontend and occupies unwanted memory space. In this exercise, you will learn how to remove the duplicate rows from the MySQL table.

For this, let's create a demo MySQL table 'students'. You can either use your existing table if you have OR copy paste these statements -

CREATE TABLE IF NOT EXISTS `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

INSERT INTO `students` (`id`, `first_name`, `last_name`, `email`) VALUES
(1, 'John', 'Smith', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(2, 'Soyam', 'Mithal', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(3, 'Rohan', 'Soy', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(4, 'Rita', 'Smith', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(5, 'John', 'Smith', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(6, 'Sayam', 'Mitra', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(7, 'Shyam', 'Mishra', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(8, 'Soyam', 'Mithal', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(9, 'Rohan', 'Soy', This email address is being protected from spambots. You need JavaScript enabled to view it.'),
(10, 'Mita', 'Dahl', This email address is being protected from spambots. You need JavaScript enabled to view it.');

Solutions

There are several methods to delete duplicate rows from a MySQL table.

Method 1: Keep the highest or lowest ID

Here, we have used the greater than or less than operators to delete duplicate rows from a table.

DELETE s1 FROM students s1, students s2 
WHERE s1.id < s2.id AND s1.first_name = s2.first_name

Output of the above statement

+----+------------+-----------+-------------------------+
| id | first_name | last_name | email                   |
+----+------------+-----------+-------------------------+
|  4 | Rita       | Smith     | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
|  5 | John       | Smith     | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
|  6 | Sayam      | Mitra     | This email address is being protected from spambots. You need JavaScript enabled to view it. |
|  7 | Shyam      | Mishra    | This email address is being protected from spambots. You need JavaScript enabled to view it.       |
|  8 | Soyam      | Mithal    | This email address is being protected from spambots. You need JavaScript enabled to view it.    |
|  9 | Rohan      | Soy       | This email address is being protected from spambots. You need JavaScript enabled to view it.   |
| 10 | Mita       | Dahl      | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
+----+------------+-----------+-------------------------+

DELETE s1 FROM students s1, students s2 
WHERE s1.id > s2.id AND s1.first_name = s2.first_name

Output of the above statement

+----+------------+-----------+-------------------------+
| id | first_name | last_name | email                   |
+----+------------+-----------+-------------------------+
|  1 | John       | Smith     | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
|  2 | Soyam      | Mithal    | This email address is being protected from spambots. You need JavaScript enabled to view it.    |
|  3 | Rohan      | Soy       | This email address is being protected from spambots. You need JavaScript enabled to view it.   |
|  4 | Rita       | Smith     | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
|  6 | Sayam      | Mitra     | This email address is being protected from spambots. You need JavaScript enabled to view it. |
|  7 | Shyam      | Mishra    | This email address is being protected from spambots. You need JavaScript enabled to view it.       |
| 10 | Mita       | Dahl      | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
+----+------------+-----------+-------------------------+

Method 2: Use DELETE JOIN Statement

Here, we have used the MySQL INNER JOIN on the same table and delete the duplicate rows.

DELETE s1 FROM students s1
INNER JOIN students s2 
WHERE s1.id < s2.id AND 
s1.first_name = s2.first_name;

Output of the above statement

+----+------------+-----------+-------------------------+
| id | first_name | last_name | email                   |
+----+------------+-----------+-------------------------+
|  4 | Rita       | Smith     | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
|  5 | John       | Smith     | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
|  6 | Sayam      | Mitra     | This email address is being protected from spambots. You need JavaScript enabled to view it. |
|  7 | Shyam      | Mishra    | This email address is being protected from spambots. You need JavaScript enabled to view it.       |
|  8 | Soyam      | Mithal    | This email address is being protected from spambots. You need JavaScript enabled to view it.    |
|  9 | Rohan      | Soy       | This email address is being protected from spambots. You need JavaScript enabled to view it.   |
| 10 | Mita       | Dahl      | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
+----+------------+-----------+-------------------------+

Method 3: Use Temporary Table

Here, we have removed the redundant data by creating a temporary table. These processes need several steps -

STEP1: In this, first we create a copy of 'students' table

CREATE TABLE students_temp 
LIKE students;

STEP2: Insert data into 'students_temp' table -

INSERT INTO students_temp
SELECT * 
FROM students
GROUP BY first_name;

STEP3: Next, delete 'students' table -

DROP TABLE students;

STEP4: Rename 'students_temp' table to 'students' table -

ALTER TABLE students_temp 
RENAME TO students;

Method 4: Use the MAX() or MIN()

Here, we have used MySQL MAX() or MIN() methods to delete redundant data.

DELETE FROM students
WHERE id NOT IN (
    SELECT * 
    FROM (
        SELECT MIN(id)
        FROM students
        GROUP BY first_name
    ) temp
)

Output of the above statement

+----+------------+-----------+-------------------------+
| id | first_name | last_name | email                   |
+----+------------+-----------+-------------------------+
|  1 | John       | Smith     | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
|  2 | Soyam      | Mithal    | This email address is being protected from spambots. You need JavaScript enabled to view it.    |
|  3 | Rohan      | Soy       | This email address is being protected from spambots. You need JavaScript enabled to view it.   |
|  4 | Rita       | Smith     | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
|  6 | Sayam      | Mitra     | This email address is being protected from spambots. You need JavaScript enabled to view it. |
|  7 | Shyam      | Mishra    | This email address is being protected from spambots. You need JavaScript enabled to view it.       |
| 10 | Mita       | Dahl      | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
+----+------------+-----------+-------------------------+

DELETE FROM students
WHERE id NOT IN (
    SELECT * 
    FROM (
        SELECT MAX(id)
        FROM students
        GROUP BY first_name
    ) temp
)

Output of the above statement

+----+------------+-----------+-------------------------+
| id | first_name | last_name | email                   |
+----+------------+-----------+-------------------------+
|  4 | Rita       | Smith     | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
|  5 | John       | Smith     | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
|  6 | Sayam      | Mitra     | This email address is being protected from spambots. You need JavaScript enabled to view it. |
|  7 | Shyam      | Mishra    | This email address is being protected from spambots. You need JavaScript enabled to view it.       |
|  8 | Soyam      | Mithal    | This email address is being protected from spambots. You need JavaScript enabled to view it.    |
|  9 | Rohan      | Soy       | This email address is being protected from spambots. You need JavaScript enabled to view it.   |
| 10 | Mita       | Dahl      | This email address is being protected from spambots. You need JavaScript enabled to view it.        |
+----+------------+-----------+-------------------------+