Find and Remove Duplicate Data From MYSql table

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

How to Find and Remove Duplicate Data From MYSql table:

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.

Insurance_Data

1) Find Duplicate Data From MYSql table:

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
);

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
);

After using the query your table look like as below.

Now You have successfully find the data records in your mysql table.

2) Remove Duplicate Data From 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;

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;

Enjoy find and remove duplicate data from mysql table.

Read About Other Interesting Things below.

1. HOW TO WRITE CSS CODE FOR SAFARI BROWSER ONLY (NOT CHROME)
2. ADD GOOGLE CUSTOM SEARCH ENGINE ON YOUR WEBSITE
3. DISABLE ADDING NOODP META ROBOTS TAG IN YOAST PLUGIN
4. Basics of Social Media Optimization Services
5. On Page Search Optimization Services
6. How to Create a Website from scratch for beginners
7. How To Write a Blog for Website and Content Marketing
8. Website Promotion – Need For All Websites
9. Important Things to Think Before Graphic Designs
10. Remember Things Before Email Marketing

If You like my blog, kindly Subscribe it.

Categories: Website Design