Home » Uncategorized

Remove duplicate entries / rows from a mySQL database table

11 October 2007 81 views Comments Permanent Link

Was searching for the solution to remove the duplicate rows in a column from a mySQL table came across this easy work around.

————————————————————————————
This is an extremely quick and painless way to remove duplicate rows (tuples) from a MySQL database table. The best part of it is that it requires no programming or PHP coding whatsoever, it can all be done with three manual SQL queries! Note: this only works on MySQL 3.23 and above. But I have a hard time imagining anyone running an older version than that anyways!

So, there are 3 steps, and therefore 3 SQL statements:

Step 1: Move the non duplicates (unique tuples) into a temporary table

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

Step 2: delete delete the old table
We no longer need the table with all the duplicate entries, so drop it!

DROP TABLE old_table;

Step 3: rename the new_table to the name of the old_table

RENAME TABLE new_table TO old_table;

And of course, don’t forget to fix your buggy code to stop inserting duplicates!

————————————————————————————

Source: http://www.justin-cook.com/wp/2006/12/12/remove-duplicate-entries-rows-a-mysql-database-table

Related Posts with Thumbnails

Popularity: 1% [?]

PR: 0

Tags: , , ,
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...
  • /* Some more Queries for more insights: */
    SELECT a.* , b.primary_key AS primary_key_alias
    FROM `tbl_name` a, `tbl_name` b
    WHERE a.primary_key != b.primary_key
    AND a.`field_for_duplicates` = b.`field_for_duplicates`
  • Ben
    dude! you rock because i was trying to figure out how to do this without doing too much work!
blog comments powered by Disqus
Olark Livehelp