Delete Duplicate Rows in MySQL

Duplicate cleanup should start by defining which columns make two rows duplicates and by previewing the rows before deleting anything. The examples below treat name, class, mark, and sex as the duplicate data while id is the unique row identifier.

Find duplicate groups first

SELECT name, class, mark, sex, COUNT(*) AS copies
FROM student_duplicate
GROUP BY name, class, mark, sex
HAVING COUNT(*) > 1;

Keep the lowest ID and delete later duplicates

DELETE t1
FROM student_duplicate AS t1
INNER JOIN student_duplicate AS t2
    ON t1.name = t2.name
   AND t1.class = t2.class
   AND t1.mark = t2.mark
   AND t1.sex = t2.sex
   AND t1.id > t2.id;

Reverse the ID comparison if your requirement is to keep the highest ID instead.

DELETE t1
FROM student_duplicate AS t1
INNER JOIN student_duplicate AS t2
    ON t1.name = t2.name
   AND t1.class = t2.class
   AND t1.mark = t2.mark
   AND t1.sex = t2.sex
   AND t1.id < t2.id;

Remove rows that are completely identical

If the table itself allows duplicate IDs because no PRIMARY KEY exists, a structure-preserving rebuild can remove rows that are identical across all columns.

CREATE TABLE student_duplicate_clean LIKE student_duplicate2;

INSERT INTO student_duplicate_clean
SELECT DISTINCT *
FROM student_duplicate2;

After checking the cleaned table, swap it into place instead of dropping the original before validation.

RENAME TABLE
    student_duplicate2 TO student_duplicate_backup,
    student_duplicate_clean TO student_duplicate2;

This approach uses CREATE TABLE ... LIKE so indexes and table attributes are preserved more reliably than with a plain CREATE TABLE ... SELECT.

Download the sample SQL dump

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: