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 …[Read more]