Hi Friends, Today i will tell you about how to find and remove duplicate data from mysql table. It is very useful query to find the duplicate record from any mysql or mysqli database tables.
When we work on large amount of database, we face a problem for finding and removing the duplicate data from the mysql table. In the interview, generally interviewer ask questions about it.
For Handling the database duplicate records, You have to find it and remove it.
For More information about Website Design you can click below:
Pure CSS Button With Lightening Glowing and Shining Effect
Create Expand Collapse Sliding Menu Using jQuery
Background Color Change Automatically Using Jquery
How to add smart slider Facebook Like Box Pop-up
Learn Ajax For Beginners In Hindi
33 New Awesome Features in CSS3 and CSS4
Simple Show Hide Accordion Code Using Jquery
Add Diwali Fireworks Using Jquery In Your Website
Add CKEditor in Html Form using Jquery
Jquery Me Simple Slider Kaise Bnate Hai
Learn JQUERY Code Step By Step For Beginners
You have to follow some steps to find the duplicate data and remove it. I will explain you steps by steps.
Assume you have a database with 50,000 insurance data records.
ID | Insurance_company | Address | Mobile | |
---|---|---|---|---|
1 | Aviva India | Faridabaad | dummy@website.com | 9999999999 |
2 | Bajaj Allianz Life Insurance | Delhi | temperory@website.com | 9999988888 |
3 | Aviva India | Faridabaad | dummy@website.com | 9999999999 |
-- | -- | -- | -- | -- |
-- | -- | -- | -- | -- |
As like you see in the above table 2 records are duplicate. I tell you the query, using this you can find the duplicate records in the mysql table.
SELECT Insurance_company FROM Insurance_Data WHERE Insurance_company IN ( SELECT Insurance_company FROM Insurance_Data GROUP BY Insurance_company HAVING COUNT( Insurance_company ) >1 ); |
SELECT Insurance_company FROM Insurance_Data WHERE Insurance_company IN ( SELECT Insurance_company FROM Insurance_Data GROUP BY Insurance_company HAVING COUNT( Insurance_company ) >1 );
Syntax of searching duplicate data in Mysql table:
SELECT Column_Name FROM Table_Name WHERE Column_Name IN ( SELECT Column_Name FROM Table_Name GROUP BY Column_Name HAVING COUNT( Column_Name ) >1 ); |
SELECT Column_Name FROM Table_Name WHERE Column_Name IN ( SELECT Column_Name FROM Table_Name GROUP BY Column_Name HAVING COUNT( Column_Name ) >1 );
After using the query your table look like as below.
ID | Insurance_company | Address | Mobile | |
---|---|---|---|---|
1 | Aviva India | Faridabaad | dummy@website.com | 9999999999 |
3 | Aviva India | Faridabaad | dummy@website.com | 9999999999 |
Now You have successfully find the data records in your mysql table.
In the above process we search the duplicate data. Now we have to remove it from our database. See the delete duplicate query below.
DELETE C1 FROM Insurance_Data C1,Insurance_Data C2 WHERE C1.ID < C2.ID AND C1.Insurance_company = C2.Insurance_company; |
DELETE C1 FROM Insurance_Data C1,Insurance_Data C2 WHERE C1.ID < C2.ID AND C1.Insurance_company = C2.Insurance_company;
Syntax of deleting duplicate data in Mysql table:
DELETE C1 FROM Table_Name C1,Table_Name C2 WHERE C1.ID < C2.ID AND C1.Column_Name = C2.Column_Name; |
DELETE C1 FROM Table_Name C1,Table_Name C2 WHERE C1.ID < C2.ID AND C1.Column_Name = C2.Column_Name;
Read About Other Interesting Things below.
If You like my blog, kindly Subscribe it.
Categories: Website Design
Leave a Reply