INSERT AFTER and increase values below
Posted: Wed Nov 14, 2007 9:05 pm
I've created a very simple ranking system - it allows a user to enter a rank title, and its weight (the lower the weight the more 'powerful' the rank). I have my database setup as follows:
Currently, the administrator can enter a multiple ranks with the same weight - now fixing this problem is trivial. What I want is a little different. Assum my table contains some data as follows:
But the administrator comes back and realizes he also wants to add Corporal whose weight is less than a Seargant but greater than a Private. Now I'm assuming there is some sort of "INSERT AFTER" syntax that I would use to...insert after sergeant, but if the administrator entered (assuming the correct query):
title: "Corporal "
weight: 4
The table would then look like this?
With Corporal and Private having the same weight. Is there an easy to insert after "x" and bump every weight up by one below the new insertion? Or do I have to do the insertion, then make a loop - looping through all the elements below x and 'manually' increase each weight?
Code: Select all
id title weightCode: Select all
id title weight
1 Colonel 1
3 Major 2
6 Sergeant 3
7 Private 4title: "Corporal "
weight: 4
The table would then look like this?
Code: Select all
id title weight
1 Colonel 1
3 Major 2
6 Sergeant 3
8 Corporal 4
7 Private 4With Corporal and Private having the same weight. Is there an easy to insert after "x" and bump every weight up by one below the new insertion? Or do I have to do the insertion, then make a loop - looping through all the elements below x and 'manually' increase each weight?