SELECT DISTINCT / Add auto_increment field
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes.
ASKER