Updating records

[Anon]

I'm trying to shift an column of sequential numbers (auto-incremented, unique). I need to increment all the numbers above a certain number set by the user so I can insert a record into the space i just created. Kinda like this, if the user enters 2

1 -> 1 stays same
2 \
3 > 3 shifts down

4 shifts down

1
<- 2 insert here
3
4

The problem is that mysql won't let me shift the numbers because they need to be unique and it hits a duplicate for 3. I'm using this:
UPDATE table SET id = id+1 where id >= $number_entered

Tried this but got an error:
UPDATE german SET id = id+1 where id >= $number_entered order by id desc

Ideas? Please?

[sarahk]

You would probably have to do it one step at a time but the primary key for table is not usually a sortorder. Look at having an additional field to do that job. Even then I'd consider why you have to change it and is there a better logic?

consider how you decide where to insert the row. Can this decision making be automated?

[[deleted]]

"I need to increment all the numbers above a certain number set by the user so I can insert a record into the space i just created. Kinda like this, if the user enters 2"

No you don't.

auto_increment numbers identify records, and that is all they do. You don't print them on the screen, and you certainly do not change them, not ever.

read this:
http://www.yapf.net/faq.php?cmd=viewitem&itemid=32