Mysql; Insert / Update a record - re-order a priority no.

Re-ordering a list of priorities in a table

[garwil]

Hi all,

Hope someone can point me in the right direction.

I have a table with the following fields:

venue_id (int 20)
rank (int 20)

I have 10 records and each has a different "rank" like 1,2,3,4,5,6,7,8,9,10 etc. When the user views these records they are presented by "rank ASC". All cool in adding the records and viewing them as it stands BUT here comes the issue I am facing.
If I add an additional record and I want that new record to have a "rank" of 5 then I am wanting all the existing records with a rank of 5 or below 5 i.e. existing number 5 as well as 6-10 to be changed ON SUBMIT. So then the new record gets inserted as 5 and the old number 5 becomes 6 and 6 becomes 7 and so forth... Is this possible?


thanks in advance....

[Dijkgraaf]

You would first have to do an update before you insert the new one.
e.g UPDATE ranktable SET rank = rank + 1 WHERE RANK >= rankofnewrecord

[brotherhood of LAN]

And if rank is a UNIQUE or PRIMARY key you will need to do the update in descending order

[garwil]

Many thanks Dijkgraaf and brotherhood of LAN for your advice which should do it..