MySQL: Remove Duplicate Entries
Remove duplicate entries is fairly hardcore, if the identify and delete approach is used – an approach described in detail here. An other approach to the problem of duplicates is to simply add a UNIQUE index to the current table and thereby let MySQL do the hardcore identification and deletion job.
Short example of the index approach.
Assume the following table and data
CREATE TABLE myDATA (
id int(10) NOT NULL auto_increment,
a int,
b int,
c int,
updated timestamp(14),
PRIMARY KEY (id)
);
insert into myData (a,b,c) values (1,2,3);
insert into myData (a,b,c) values (1,2,3);
insert into myData (a,b,c) values (1,5,4);
insert into myData (a,b,c) values (1,6,4);
mysql> select * from dupTest;
select * from dupTest;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 2 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 3 | 1 | 5 | 4 | 2004-04-16 10:55:35 |
| 4 | 1 | 6 | 4 | 2004-04-16 10:55:35 |
+------+------+------+------+---------------------+
4 rows in set (0.00 sec)
mysql>
Note, the first two rows contains duplicates in columns a and b. It contains
other duplicates; but, leaves the other duplicates alone.
mysql> ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);
mysql> select * from dupTest;
select * from dupTest;
+——+——+——+——+———————+
| pkey | a | b | c | timeEnter |
+——+——+——+——+———————+
| 1 | 1 | 2 | 3 | 2004-04-16 11:11:42 |
| 3 | 1 | 5 | 4 | 2004-04-16 11:11:42 |
| 4 | 1 | 6 | 4 | 2004-04-16 11:11:42 |
+——+——+——+——+———————+
3 rows in set (0.00 sec)
Related posts:
- Mysql Data Import
- MDB Tools to export (migrate) from mdb (Microsoft Access format) to MySQL
- Sun acquires MySQL
- MySQL Optimization and Performance Tips
- Optimize MySQL Performance With MySQLTuner
- Optimize MySQL for Low Memory Use
- MySQL Concatenate: Adding String At The End Of Field Data
- Howto: How to Reset the MySQL Root Password
- Set MySQL Auto Increment Manually
- Compressing mysqldump output – mysql, gzip, bzip2 and LZMA (7z)
Popular Related Items »
Incoming search terms
- mysql remove duplicates
- mysql delete duplicates
- mysql remove duplicate rows
- mysql delete duplicate rows
- remove duplicates mysql
- mysql remove duplicate
- mysql find duplicates
- mysql delete duplicate
- mysql remove duplicate entries
- mysql delete duplicate entries
- mysql remove duplicate records
- mysql delete duplicate records
- mysql find duplicate entries
- remove duplicate rows mysql
- remove duplicates in mysql
- mysql delete duplicate entry
- remove duplicate mysql
- delete duplicates mysql
- mysql delete double entries
- delete duplicate rows in mysql
- how to remove duplicate records in mysql
- remove duplicate records mysql
- remove duplicate rows in mysql
- remove duplicates from mysql
- delete duplicate entries mysql
- mysql delete double records
- mysql select remove duplicates
- delete duplicate mysql
- remove duplicate entries mysql
- mysql check for duplicates
- mysql Duplicate entry
- mysql remove doubles
- delete duplicate rows mysql
- delete duplicates in mysql
- mysql duplicate entries
- delete duplicate records mysql
- mysql delete duplicate fields
- mysql find duplicate fields
- mysql remove entry
- mysql removing duplicates
- removing duplicates in MySql
- delete duplicate entry in mysql
- delete duplicate entry mysql
- delete duplicate records in mysql
- duplicate entry mysql
Tony said,
March 20, 2008 @ 13:37
I just wanted to say thank you… I looked everywhere trying to find a way to move duplicates in sql without having to write alot of code or create new database backups etc..
ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);
lovely jublee
thank you
Tomy said,
June 15, 2008 @ 17:21
Great! Cheers!
myrddin said,
December 30, 2008 @ 0:53
Nice, very nice ! Thank you, very fast !!
Jack M said,
February 1, 2009 @ 14:00
if index cannot ne redefined:
1)
{
$x = 1;
while($x <= $i)// we loop inside the loop – this can mean quite a lot of operations so think carefully about the limit above
{
if($entry[$i] == $entry[$x])// checking string against previous entries
{
$delete_duplicate = mysql_query(“DELETE FROM `table_name` WHERE `id`=’”.$id['id'].”‘”); //delete duplicate
/*
or if wish to check first then add another field in the table (e.g. `check`) and change query into
something like this:
$mark_duplicate = mysql_query(“UPDATE `table_name` SET `check`=’1′”);
then if happy run something lie this:
$delete = mysql_query(“DELETE FROM `table_name` WHERE `check`=’1′”)
*/
echo ‘Entry #’.$id[$i].’ was the same as entry ‘.$id[$x].’ and has been deleted.’;
}
$x++;
}
}
$i++;
}
?>
zasssssssss said,
February 20, 2009 @ 7:45
xxxxsZZZZZZZZZZZZZZZZZZZZ
delilah said,
March 6, 2009 @ 4:12
Thanks, good stuff!
kaamkaaj said,
April 15, 2009 @ 8:31
Is there any short way of doing this, it is too complex for a beginner like me.
MySQL: Zeilen mit doppelten Werten löschen | What I know is what you get said,
June 28, 2009 @ 13:42
[...] Erkenntnis ist allerdings nicht auf meinen Mist gewachsen, sondern stammt von http://mediakey.dk/~cc/mysql-remove-duplicate-entries/. Subscribe to comments Comment | Trackback | Post Tags: duplicate rows, [...]
Marcin said,
October 6, 2009 @ 11:49
You solution can remove good entries and keep old ones.
For example there can be two duplicates with id 5, and 200 and obviously 200 is most up-to-date, but when you execute your SQL 200 will be removed and 5 will stay.
HOW-TO: Delete Duplicate SQL - MySQL | ShamHardy.com said,
February 15, 2010 @ 18:11
[...] http://mediakey.dk/~cc/mysql-remove-duplicate-entries/ mysql > ALTER IGNORE TABLE tablename ADD UNIQUE INDEX(value1, [...]