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
To retain the record with lower id and delte the record with higher id.
Retain the record with higher id and delete the record with lower id
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.
We lost the ID here, but by including ID we can't define the records as duplicate.
Duplicate records with duplicate idsWatch first three records.
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.
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.
This article is written by plus2net.com team.
▼ More on getting records from table with different combinations of commands