This actually is more tricky than it sounds. Firstly the table already exists. So create table is an option only if you were going to recreate a new table with an auto_increment column, then move the rest of the data (except for the index column) in to the new table, drop the old table and re-name the new table.
a shorter approach maybe to drop the column and add a new auto_increment column with auto_increment of 1000 or whatever you like. and secondly SET INSERT_ID works for values greater than LAST_INSERT_ID as expect but for values less than LAST_INSERT_ID (e.g. gaps created by delete command) only works for the next generated value. for instance if LAST_INSERT_ID is 7 and you delete the last 5 records... then SET INSERT_ID = 3; INSERT INTO table values (null, ..), (null, ..); will result in: 1 4 // first inserted record 8 // second inserted record -- id is 8 not 5! but SET INSERT_ID = 60; INSERT INTO table values (null, ..), (null, ..); will result in 1 61 // first inserted record after set 62 // second inserted record -- follows the first The logic of AUTO_INCREMET is consistent but its realization is somewhat unexpected at the beginning. --- Sherzod Ruzmetov <[EMAIL PROTECTED]> wrote: > : Am Donnerstag, 30. Januar 2003 20:15 schrieb > Mike Doanh Tran: > : > Hi all, > : > > : > I am creating a new table with an > auto_increment primary key. > : > How do i tell mysql to start incrementing at > a certain > : value, let say > : > 1000 instead of 1? > : > > : > Thanks, > : > > : > MT > : > : SET INSERT_ID=1000 does it. > > > Tabels have "AUTO_INCREMENT" option, which you will > need to set while > creating the table. In the following example id will > start with 1000: > > CREATE TABLE test ( > id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, > ) AUTO_INCREMENT="1000"; > > Good luck > > Sherzod > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
