Page 1 of 1

Inserting rows and modifiying existing auto_increment values

Posted: Thu May 13, 2004 8:02 am
by webcan
Hey there:

Can anyone tell me if there is a way to insert a row into a database that has an auto_increment field and use an existing auto_increment value, and have the following values automatically bump themselves up?

For example, I have a field, "id" with 10 records. The id value is 1 ... 10. I want to insert a new record with id value 8, so that the existing 8 becomes 9, 9 becomes 10, and 10 becomes 11. And the new next auto_increment value is 12.

Any ideas?
Thanks,
Peter.

Posted: Thu May 13, 2004 8:33 am
by tim
i think the best you'll get is the command: TRUNCATE

read on it here: http://dev.mysql.com/doc/mysql/en/TRUNCATE.html

Posted: Thu May 13, 2004 9:05 am
by leenoble_uk
I've no idea if this is the best approach and I'll probably be shot down in flames for suggesting such a thing but if the id column wasn't AUTO_INCREMENT and instead was just a plain INT column then you could SET id=(id+1) WHERE id > 8.
Use your PHP code to make sure there are no duplicates in the db.