Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
 
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc
 
Removing duplicates from a MySQL table

Surely there's got to be an easy way to remove duplicate records from a MySQL table? With a well designed table that has a unique primary key, you can simply get rid of a few duplicates, but if there are a lot of 'em, it's time consuming. You could write some sort of complex delete based on a select. You could write an application to do it (it wouldn't be more that a few lines of Perl, for example) but surely there's got to be an easy way!

A neat trick for which I take no credit ... thanks to a Paul Swarthout who gave it as an answer [here] on Database Journal.

1. Create a Unique Index on the column that should be unique but isn't, using the ignore keyword to suppress error messages ... it will zap the fault records instead.

2. Optionally (if you decide you didn't really want the index ;-) ) take the index away again.

Let's see an example of that:

Reduce 11 dwarves to 7

OOOOOOoops!

alter ignore table demo add unique index dwarf_name (name(20))

All about snow white

Features of each Dwarf from Pub Quiz Help - it's not the sort of thing I know much about myself.

If you want to try this for yourself, the data may be found [here].

Aside - before you remove duplicates, be very careful to define what you actually want to do. Do you want to remove records with duplicates in a particular column (which is what this example does) or do you want to remove rows where the complete row (except for the unique ID field?) is identical?


(written 2010-02-22, updated 2010-03-03)

 
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S157 - More MySQL commands
  [4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1331] MySQL joins revisited - (2007-09-03)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [673] Helicopter views and tartans - (2006-04-06)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [515] MySQL - an FAQ - (2005-12-03)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [449] Matching in MySQL - (2005-09-24)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [159] MySQL - Optimising Selects - (2004-12-21)
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)

S161 - Data Access and Security in MySQL
  [4493] Forgotten / lost MySQL root password - (2015-05-16)
  [4491] Web Server Admin - some of those things that happen, and solutions - (2015-05-10)
  [4406] Fixing damaged MySQL tables - Error 1712 and Error 2013 - (2015-01-25)
  [4390] Checking MySQL database backups have worked (not failed) - (2015-01-10)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [1131] MySQL - Password security (authentication protocol) - (2007-04-02)
  [947] What is an SQL injection attack? - (2006-11-27)
  [647] Checking for MySQL errors - (2006-03-15)
  [535] MySQL permissions and privileges - (2005-12-20)
  [401] What is an SQL injection attack? - (2005-08-02)
  [193] The wrong MySQL - (2005-01-29)
  [192] Current MySQL and PHP paths and upgrades - (2005-01-28)


Back to
Compile but do not run PHP - syntax check only
Previous and next
or
Horse's mouth home
Forward to
Java arrays - are they true arrays or not?
Some other Articles
Calculation within objects - early, last minute, or cached?
Getting a phone line changed ...
Length, size or capacity in Java?
Java arrays - are they true arrays or not?
Removing duplicates from a MySQL table
Compile but do not run PHP - syntax check only
What does a web application look like under Tomcat?
4759 posts, page by page
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2019: 404 The Spa • Melksham, Wiltshire • United Kingdom • SN12 6QL
PH: 01225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/2647_Rem ... table.html • PAGE BUILT: Sat May 27 16:49:10 2017 • BUILD SYSTEM: WomanWithCat