Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

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

Re-ordering a list of priorities in a table

6:02 pm on Aug 1, 2012 (gmt 0)

New User

5+ Year Member

joined:June 12, 2011
posts: 15
votes: 0

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....
9:19 pm on Aug 1, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
votes: 0

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
9:36 pm on Aug 1, 2012 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
votes: 16

And if rank is a UNIQUE or PRIMARY key you will need to do the update in descending order
2:15 pm on Aug 2, 2012 (gmt 0)

New User

5+ Year Member

joined:June 12, 2011
posts: 15
votes: 0

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

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members