Delete duplicate records by using distinct query

How to identify duplicate records?
By using group by command we can list out duplicate records.

We will learn about two types of table with duplicate data

1. Duplicate records with unique ID
2. Duplicate records including duplicate ID

id	name	class	mark	sex
1	John Deo	Four	75	female
2	Max Ruin	Three	85	male
3	Arnold	Three	55	male
13	John Deo	Four	75	female
14	Max Ruin	Three	85	male
15	Arnold	Three	55	male
4	Krish Star	Four	60	female
5	John Mike	Four	60	female
6	Alex John	Four	55	male
7	My John Rob	Fifth	78	male
8	Asruid	Five	85	male
9	Tes Qry	Six	78	male
10	Big John	Four	55	female
11	Ronald	Six	89	female
12	Recky	Six	94	female

First three records have duplicate data but having different ID , here we have to delete one of the two ids assign to duplicate records.
To retain the record with lower id and delte the record with higher id.

DELETE t1 FROM student_duplicate t1, student_duplicate t2 WHERE t1.id > t2.id AND t1.name = t2.name

Retain the record with higher id and delete the record with lower id

DELETE t1 FROM student_duplicate t1, student_duplicate t2 WHERE t1.id < t2.id AND t1.name = t2.name

If we have more records then the above query till take more time , better way to do this is by creating a new temporary table by using unique records from the main table and then create the main table by using data from temporary table.

create table  temp_table select distinct name,class,mark,sex from student_duplicate

We lost the ID here, but by including ID we can't define the records as duplicate.

Duplicate records with duplicate ids

Watch first three records.

id	name	class	mark	sex
1	John Deo	Four	75	female
2	Max Ruin	Three	85	male
3	Arnold	Three	55	male
1	John Deo	Four	75	female
2	Max Ruin	Three	85	male
3	Arnold	Three	55	male
4	Krish Star	Four	60	female
5	John Mike	Four	60	female
6	Alex John	Four	55	male
7	My John Rob	Fifth	78	male
8	Asruid	Five	85	male
9	Tes Qry	Six	78	male
10	Big John	Four	55	female
11	Ronald	Six	89	female
12	Recky	Six	94	female

Create a new table by using unique data from main table student_duplicate2

create table  a1 select distinct id,name,class,mark,sex from student_duplicate2

We copied all records with the table structure to new table a1. Now you can use table a1 or copy the data back to main table after removing all records. Here is the batch query you can use to remove all duplicate data from our student_duplicate2 table.

create table  a1 select distinct id,name,class,mark,sex from student_duplicate2;  
DROP table student_duplicate2;
create table student_duplicate2 select * from a1;
DROP table a1;

First a1 the temporary table created by using unique records only.
Next line will delete the main table student_duplicate2
Next line will create the main table student_duplicate2 by using the same structure and data of temporary table a1
Next line will delete the temporary table a1. Download MySQL Dump to create tables for above examples
SQL References How to delete Records in different tabels

Subscribe to our YouTube Channel here

Delete duplicate records using SQL DISTINCT Command

Read more on Distinct Query

Duplicate records with duplicate ids