# [EMAIL PROTECTED] / 2003-06-25 02:11:05 -0400:
> 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?
doesn't simply adding the column + primary key in one ALTER TABLE
do what you want?
mysql> create table test (a char);
Query OK, 0 rows affected (0.00 sec)
mysql> insert test values ('a'), ('b'), ('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> alter table test add b int auto_increment, add primary key (b);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------+---+
| a | b |
+------+---+
| a | 1 |
| b | 2 |
| c | 3 |
+------+---+
3 rows in set (0.00 sec)
mysql> desc test;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| a | char(1) | YES | | NULL | |
| b | int(11) | | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
--
If you cc me or remove the list(s) completely I'll most likely ignore
your message. see http://www.eyrie.org./~eagle/faqs/questions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
