mysql update between row and shift current to right

how to update column value of specific id and shift after to right.

id    track
 1      3
 2      5
 3      8开发者_如何学编程
 4      9

want to update id 3 track column value to 10, result like this

id    track
 1      3
 2      5
 3      10
 4      8
 5      9

id column is auto_increment

or any suggestion it's my pleasure.

thank you.

You should avoid tweaking auto_increments. Auto increment keys are usually supposed to be used internally (e.g. for linking purposes). If you want to order tracks, i suggest you add a seperate numeric field "ordernro" to the table and update that

To add a column order nro to a table named album, do like this:

alter table album add ordernro int(2) after id;

Then copy the current value for id into this new column: update album set ordernro=id; (do this only once after adding the column)

To insert track 10 at position 3 first shift the rows: update album set ordernro = ordernro + 1 where ordernro >= 3;

And then insert track 10: insert into album (ordernro, track) values (3, 10); Remember to update your existing insert/update/select statements accordingly.

The result can be checked by:

select * from album order by ordernro; (The id will now be "mixed up", but that doesn't matter)

UPDATE table SET id = id + 1 WHERE id >= x;

x being the id where you place your current track.

The problem with JK 's answer is that MySQL returns error saying that is can't UPDATE because the index at x+1 would be duplicate.

What I did is

UPDATE table SET id = id + 100 WHERE id >= x;
UPDATE table SET id = id - 99  WHERE id >= x;

And then INSERT my row at index x