Delete duplicate row from MySQL table

need an explanation for this answer? contact us directly to get an explanation for this answer

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     | rita@example.com        |
|  5 | John       | Smith     | john@example.com        |
|  6 | Sayam      | Mitra     | sayam.mitra@example.com |
|  7 | Shyam      | Mishra    | shyam@example.com       |
|  8 | Soyam      | Mithal    | s.mithal@example.com    |
|  9 | Rohan      | Soy       | rohan.soy@example.com   |
| 10 | Mita       | Dahl      | mita@example.com        |
+----+------------+-----------+-------------------------+

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     | john@example.com        |
|  2 | Soyam      | Mithal    | s.mithal@example.com    |
|  3 | Rohan      | Soy       | rohan.soy@example.com   |
|  4 | Rita       | Smith     | rita@example.com        |
|  6 | Sayam      | Mitra     | sayam.mitra@example.com |
|  7 | Shyam      | Mishra    | shyam@example.com       |
| 10 | Mita       | Dahl      | mita@example.com        |
+----+------------+-----------+-------------------------+

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     | rita@example.com        |
|  5 | John       | Smith     | john@example.com        |
|  6 | Sayam      | Mitra     | sayam.mitra@example.com |
|  7 | Shyam      | Mishra    | shyam@example.com       |
|  8 | Soyam      | Mithal    | s.mithal@example.com    |
|  9 | Rohan      | Soy       | rohan.soy@example.com   |
| 10 | Mita       | Dahl      | mita@example.com        |
+----+------------+-----------+-------------------------+

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     | john@example.com        |
|  2 | Soyam      | Mithal    | s.mithal@example.com    |
|  3 | Rohan      | Soy       | rohan.soy@example.com   |
|  4 | Rita       | Smith     | rita@example.com        |
|  6 | Sayam      | Mitra     | sayam.mitra@example.com |
|  7 | Shyam      | Mishra    | shyam@example.com       |
| 10 | Mita       | Dahl      | mita@example.com        |
+----+------------+-----------+-------------------------+

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     | rita@example.com        |
|  5 | John       | Smith     | john@example.com        |
|  6 | Sayam      | Mitra     | sayam.mitra@example.com |
|  7 | Shyam      | Mishra    | shyam@example.com       |
|  8 | Soyam      | Mithal    | s.mithal@example.com    |
|  9 | Rohan      | Soy       | rohan.soy@example.com   |
| 10 | Mita       | Dahl      | mita@example.com        |
+----+------------+-----------+-------------------------+

need an explanation for this answer? contact us directly to get an explanation for this answer