logo
  • Home
  • About
  • Services
  • Portfolio
  • Contact Us
  • Submit Guest post
Find and Remove Duplicate Data From MYSql table

Find and Remove Duplicate Data From MYSql table

May 24, 2016 No comments Article SEO Website Designing

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

IDInsurance_companyAddressEmailMobile
1Aviva IndiaFaridabaaddummy@website.com9999999999
2Bajaj Allianz Life InsuranceDelhitemperory@website.com9999988888
3Aviva IndiaFaridabaaddummy@website.com9999999999
----------
----------

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

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.

IDInsurance_companyAddressEmailMobile
1Aviva IndiaFaridabaaddummy@website.com9999999999
3Aviva IndiaFaridabaaddummy@website.com9999999999

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;

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;

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.

Share this:

  • Tweet
  • Telegram
  • WhatsApp
  • Share on Tumblr
  • Pocket
  • Email

Related

Categories: Website Design

Tags: Mysql

Leave a Reply Cancel reply

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,504 other subscribers

Categories

  • Angular (1)
  • Content Marketing (2)
  • Email Marketing (3)
  • Graphics Design (3)
  • Keyword Strategy (1)
  • Other (3)
  • Search Engine Optimization (10)
  • Social Media Optimization (4)
  • Website Design (67)
  • WordPress (18)
  • Subscribe to Blog via Email

    Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,504 other subscribers

  • Tag Cloud

    Add Social Share button in WordPress without Plugins Bootstrap center div on screen with flexbox center div vertically and horizontally in body center div vertically and horizontally responsive Center Elements Vertically And Horizontally Using Flex Centering in CSS Create Your Own Fast Social Sharing Buttons For WordPress Creating Scroll-based Animations using jQuery and CSS3 CSS CSS3 CSS3 Variables CSS custom properties CSS Variables custom share buttons wordpress Disable Mouse Right Click Using Jquery disable right click with jquery Disabling right mouse click menu facebook Get to Know the CSS Object Fit and Position Properties How Can You Use CSS Variables HOW TO CODE A RESPONSIVE EMAILER OR NEWSLETTER How to Create Social Sharing Button without any Plugin HOW TO DESIGN A RESPONSIVE EMAILER OR NEWSLETTER how to disable right click menu in html page How to prevent Right Click option using jquery How to Restore WordPress Website from Backup Manually How to use CSS Variables HTML HTML5 javascript jquery jQuery make div slide in when scrolling down page Know About CSS Object Fit and Position Properties Meta Tags Mysql object-fit object-fit/object-position object-position Responsive Website Sliding Div When Scroll Down Page Using Jquery Vertical Centering - Solved by Flexbox Webpage Scrolling Animation Effects with CSS3 & jQuery What You Need To Know About CSS Variables wordpress
  • Copyright SEO Website Designing 2015

    loading Cancel
    Post was not sent - check your email addresses!
    Email check failed, please try again
    Sorry, your blog cannot share posts by email.