You need simply add the column predefined as AUTO_INCREMENT and PRIMARY
KEY:
CREATE TABLE incrtest (
name varchar(100) NOT NULL
) TYPE=MyISAM;
INSERT INTO incrtest VALUES("ben");
INSERT INTO incrtest VALUES("bob");
INSERT INTO incrtest VALUES("bom");
INSERT INTO incrtest VALUES("gddo");
INSERT INTO incrtest VALUES("billy");
ALTER TABLE incrtest ADD prikey INT auto_increment PRIMARY KEY;
mysql> SELECT * FROM incrtest;
+-------+--------+
| name | prikey |
+-------+--------+
| ben | 1 |
| bob | 2 |
| bom | 3 |
| gddo | 4 |
| billy | 5 |
+-------+--------+
5 rows in set (0.00 sec)
As you can see, the values are added automatically.
Regards,
Miek Hillyer
www.vbmysql.com
> -----Original Message-----
> From: John Hicks [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, June 25, 2003 12:11 AM
> To: [EMAIL PROTECTED]
> Subject: Initializing primary key values for existing table
>
>
> I needed to add a new, autoincrementing, primary key column
> to a table and have been struggling to figure out how to
> assign an initial, unique value to each row.
>
> I finally accomplished my task, but feel sure there's an
> easier way.
>
> Here is my solution:
>
> 1. Add the column:
> alter table mytable add mycolumn int auto_increment;
>
> 2. Set up a user variable:
> @mycounter = 0;
>
> 3. Assign the initial values by incrementing the counter:
> Update mytable set mycolumn = max((@mycounter := @mycounter
> + 1), @mycounter);
>
> 4. Finally, set the column to be the primary key:
> alter table mytable set primary key mycolumn;
>
> This seems like a roundabout way of doing things. Can any
> of you improve on it?
>
> Thanks,
>
> John
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
