Saturday 10 September 2016

How to delete duplicate records from a table in MYSQL ?


Table Name : records
Procedure 1 :
Step 1 :
Create new table by removing duplicates
CREATE TABLE uniquerecords AS SELECT * FROM records GROUP BY name HAVING ( COUNT(name)>0 )
Step 2 :
Delete old table
DROP TABLE records
Step 3 :
Rename the New Table to Old Table
RENAME TABLE uniquerecords TO records
Procedure 2 : (IF Ajay is repeated five times, the below query would delete Ajay four times.)
QUERY :

DELETE FROM records USING records, records AS virtualtable WHERE (records.sno>virtualtable.sno) AND (records.name=virtualtable.name)
This would delete all the records except the first one.
Procedure 3 : (IF Ajay is repeated five times, the below query would delete all the five records.)
QUERY :

DELETE FROM records USING records, records AS virtualtable WHERE (records.sno=virtualtable.sno) AND (records.name=virtualtable.name)

No comments:

Post a Comment

Your comment is so valuable as it would help me in my growth of knowledge.