Q:

MySQL/PHP How would I insert an row into mysql and increase all the auto incremented fields below by 1

say i have a table like so:
id|name  |
----------
1 |fred  |
2 |jim   |
3 |tracy |
4 |stacy |

"id" is the primary index with auto increment.
I would like to add a row below "#2 - jim". I'll call this new row "john".
How could I insert "john" below "jim" so john had the "id" of 3 and all the id's of the names below "john" would go up by 1?
e.g.
id|name  |
----------
1 |fred  |
2 |jim   |
3 |john  |
4 |tracy |
5 |stacy |

Many Thanks

A:

This command will add +1 to everyone's id after jim.
UPDATE your_table SET id=id+1 WHERE id>2;

This command will insert a row just after jim.
INSERT INTO your_table(id,name) VALUES(3,'john');

It is a very bad idea to change the primary key value. If your table has relationships with other tables, there will be consisitency issue. Let's say it is customer table and there is an order table. The above idea will make trstacy's orders will be connected to tracy and tracy's john, etc.

