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 :D

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?