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

idnameclassmarksex
1John DeoFour75female
2Max RuinThree85male
3ArnoldThree55male
13John DeoFour75female
14Max RuinThree85male
15ArnoldThree55male
4Krish StarFour60female
5John MikeFour60female
6Alex JohnFour55male
7My John RobFifth78male
8AsruidFive85male
9Tes QrySix78male
10Big JohnFour55female
11RonaldSix89female
12ReckySix94female
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.

Read more on Distinct Query

Read more on how to create table by using data from an existing table.

Duplicate records with duplicate ids

Watch first three records.
idnameclassmarksex
1John DeoFour75female
2Max RuinThree85male
3ArnoldThree55male
1John DeoFour75female
2Max RuinThree85male
3ArnoldThree55male
4Krish StarFour60female
5John MikeFour60female
6Alex JohnFour55male
7My John RobFifth78male
8AsruidFive85male
9Tes QrySix78male
10Big JohnFour55female
11RonaldSix89female
12ReckySix94female
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.
Your Rating




Post your comments , suggestion , error , requirements etc here .








We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer