Page 1 of 1
auto-increment
Posted: Tue Jan 21, 2003 2:43 pm
by sirTemplar
i would like to add a field on my database that i would like to auto-increment with starting value as 10001, is that possible? how? what should be the type varchar or tinyint? i already have more than 6000 data! thanx!
Re: auto-increment
Posted: Tue Jan 21, 2003 3:18 pm
by puckeye
sirTemplar wrote:i would like to add a field on my database that i would like to auto-increment with starting value as 10001, is that possible? how? what should be the type varchar or tinyint? i already have more than 6000 data! thanx!
Your field will have to be at least a smallint (values up to 65535 if unsigned) or probably better as mediumint (values up to 16777215 unsigned). Tinyint has a maximum value of 256 unsigned.
To add a new field you'll have to alter your table with the following query:
Code: Select all
ALTER TABLE tbl_name ADD 'auto' MEDIUMINT(10) UNSIGNED NOT NULL AUTO_INCREMENT
I named my field "auto". Note that you can have only one auto-increment field. Now to make the field start at 10001 you'll have to create another query.
After the ALTER TABLE your new auto-increment will probably start at 0 so you sedn another query adding 10001 to that field. Something like:
Code: Select all
UPDATE tbl_name SET 'auto' + 10001
NOTE: I did not test those because I have not tables to test them onto (all my tables already have auto-increment and I don't want to play with those). Anyway I would suggest you make a copy of your current table and make tests on the copy.
thanks
Posted: Wed Jan 22, 2003 3:00 am
by sirTemplar
thanks pal

Posted: Wed Jan 22, 2003 9:30 am
by puckeye
How well did the code work?
Posted: Wed Jan 22, 2003 9:58 am
by Wayne
when you created the table you should have had
Code: Select all
CREATE TABLE test ( col1 int primary key auto_increment not null,
col2 text
)
AUTO_INCREMENT=10001
this would have made your first value 10001
Posted: Wed Jan 22, 2003 11:26 am
by puckeye
Wayne wrote:when you created the table you should have had
Code: Select all
CREATE TABLE test ( col1 int primary key auto_increment not null,
col2 text
)
AUTO_INCREMENT=10001
this would have made your first value 10001
Yes you are right but how do you ADD a field with an autoincrement starting at 10001?