After I moved back to Europe and Malta in order to set up our operations here, I was approached by a old friend of mine who wanted to know how to add a UNIQUE constraint and remove duplicates on a table, while keeping the newest records. He had been trying with ALTER TABLE but ran into problems as the older values were taken.

So, to help him out, I first solved it based on his original idea, and then figured I would post a small note about the solution here.

So, let’s say we have the following structure . . .

sql01 blogs> SHOW CREATE TABLE post1164\G
*************************** 1. row ***************************
       Table: post1164
Create Table: CREATE TABLE `post1164` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(5) DEFAULT NULL,
  `c` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

. . . with a small amount of data in it:

sql01 blogs> SELECT * FROM post1164;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | a    | a1   |
| 2 | a    | a2   |
| 3 | b    | b1   |
| 4 | c    | c2   |
| 5 | b    | b2   |
| 6 | c    | c1   |
+---+------+------+
6 rows in set (0.00 sec)

Now, if I were to use his original SQL, I would get a result similar to this:

(more…)