SELECT DISTINCT / Add auto_increment field
Posted on 2005-04-25
I have a table:
CREATE TABLE `asdf` (
`asdf` int(10) unsigned NOT NULL auto_increment,
`sdfg` varchar(30) NOT NULL default '',
PRIMARY KEY (`asdf`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
Whereby I use CREATE TABLE tmp SELECT DISTINCT sdfg FROM asdf; to eliminate duplicates.
Then I drop the original table, asdf, and rename tmp to asdf.
So the duplicates are gone. My question is this:
How do I add a field to this table that is of type UNSIGNED INT, AUTO_INCREMENT, and is the PRIMARY KEY of the table, AND... that numbers each record in the table starting at 1?