Inserting rows and modifiying existing auto_increment values

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
webcan
Forum Commoner
Posts: 66
Joined: Tue Oct 28, 2003 2:25 pm
Location: Toronto, Canada

Inserting rows and modifiying existing auto_increment values

Post 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.
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post 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
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post 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.
Post Reply