MySQL Delete dupes

My favorite way of deleting dupes (taken from the honorable #mysql on freenode)


If you have a unique ID and the name may contain duplicates then

DELETE t1 FROM table1 AS t1 JOIN table1 AS t2 ON t1.id>t2.id AND t1.name=t2.name;

If you have other fields that need to be taken into consideration extend the join as needed. If it fails on a myisam table see http://bugs.mysql.com/bug.php?id=28837

Comments

Post a Comment

Popular posts from this blog

waoo port forwarding broken

For anyone reaching this page, let me first congratulate you on getting fiber, and also saying I'm sorry for having to coop with a beta-router from waoo / tre-for that looks like it's from 1990. So you want to access your ssh on your many machines from the internet? Sounds easy enough, the administration has this setting. Forward something like 123 to -> 22 on 192.168.1.5 and you're all set to go right? Wrong , it just doesn't work. Virtual ports are just a no go on the 1.0 firmware, after writing to tre-for and waiting for a couple of days I get an answer saying you can't do range port forwards, but you can do port to port . In which I simply replied 123 -> 22 is not a range forward but a simple port forward . Two days later I get an email saying that the router is now updated and that I should try again. Did it work? YES, the very basic function of a router now works as it should and my machines can get booted of the internet once again from my phone
Read more

How to compile Percona XtraDB Cluster 5.6 on Ubuntu 14.04 Trusty Tahr

Getting a new server with the latest LTS started out like great news, but soon turned out to be a "couple" of hours wasted on google'n without success. #percona on freenode is sadly still very very idle and of no help, and after reading different sources such as http://www.percona.com/doc/percona-xtradb-cluster/5.5/installation/compiling_xtradb_cluster.html http://www.percona.com/doc/percona-xtradb-cluster/5.6/installation/compiling_xtradb_cluster.html https://mariadb.com/kb/en/building-the-galera-wsrep-package-on-ubuntu-and-debian/ I managed to get it all working and wanted to share the success. Note: There might be more packages that need to be installed that I already had when installing another application, if you get an error along the way, leave a comment and I'll help out and update the blog as we go along. apt-get install bzr openssl cmake libncurses5-dev bison xinetd socat build-essential flex bison automake autoconf bzr libtool libaio-dev zlib1
Read more

Building XtraBackup for Mac OSX

source https://twindb.com/building-xtrabackup-for-mac-os/ The above source was missing some steps which I thought I'd add as a reminder for future installations for anyone missing the same dependencies as I did. port install libgcrypt libev If you get the following error as I did on make percona-xtrabackup-2.4.1/sql/mysqld.cc:6409:10: error: use of undeclared identifier 'yaSSL_ASN1_TIME_to_string' return yaSSL_ASN1_TIME_to_string(time, buf, len); ^ [ 98%] Building CXX object sql/CMakeFiles/sql.dir/protocol_callback.cc.o 1 error generated. make[2]: *** [sql/CMakeFiles/sql.dir/mysqld.cc.o] Error 1 make[2]: *** Waiting for unfinished jobs.... make[1]: *** [sql/CMakeFiles/sql.dir/all] Error 2 make: *** [all] Error 2 then you can edit ./sql/mysqld.cc and on line 6404 change #ifdef HAVE_YASSL to #ifdef HAVE_YASSL2 and run make again, done!
Read more