Delete duplicate records using SQL DISTINCT Command
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.
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;