I have a SQL Server table for user skills with priority order for each user as this:
+----+------+----------+----------+
| ID | user | skill | priority |
+----+------+----------+----------+
| 1 | foo | swimming | 1 |
+----+------+----------+----------+
| 2 | foo | running | 2 |
+----+------+----------+----------+
| 3 | foo | hunting | 3 |
+----+------+----------+----------+
| 4 | boo | swimming | 1 |
+----+------+----------+----------+
| 5 | moo | swimming | 1 |
+----+------+----------+----------+
| 6 | moo | running | 2 |
+----+------+----------+----------+
How can I write SQL code to re-order the priority column values (an integer) for all skills for a user when the priority value is changed for one of the skills?
For example: for user "foo" I will change the priority of the skill "swimming" from (1) to (2); the update statement must also change the priority for all other skills for that user, in a dynamic way.
So in that example "swimming" will be priority (2) instead of (1), running will be (1) instead of (2), and the others will remain the same.
In this answer I'm assuming that you want to do this in SQL, not in C#. Based on that assumption, these two SQL statements inside a single transaction increase the priority of the specified skill by 1.
'Set @User to the required user and @Skill to the required skill.
'Decrease the priority of the user's skill above the specified skill.
UPDATE MyTable
SET priority = priority + 1
WHERE user = @User
AND priority = (SELECT priority - 1
FROM MyTable
WHERE user = @User
AND skill = @Skill)
'Increase the specified skill's priority.
UPDATE MyTable
SET priority = priority - 1
WHERE user = @User
AND skill = @Skill
AND priority > 1
In a similar fashion, these two SQL statements increase the specified skill to the specified priority.
'Set @User to the required user and @Skill to the required skill.
'Set @NewPriority to the new priority.
'Decrease the higher-prioritised skills.
UPDATE MyTable
SET priority = priority + 1
WHERE user = @User
AND priority >= @NewPriority
AND priority < (SELECT priority
FROM MyTable
WHERE user = @User
AND skill = @Skill)
'Set the specified skill's priority as requested.
UPDATE MyTable
SET priority = @NewPriority
WHERE user = @User
AND skill = @Skill
AND priority > 1
And these three SQL statements move the specified skill to the specified priority.
'Set @User to the required user and @Skill to the required skill.
'Set @NewPriority to the new priority.
'Decrease the higher-prioritised skills to
'handle case where new priority is higher.
UPDATE MyTable
SET priority = priority + 1
WHERE user = @User
AND priority >= @NewPriority
AND priority < (SELECT priority
FROM MyTable
WHERE user = @User
AND skill = @Skill)
'Increase the lower-prioritised skills to
'handle case where new priority is lower.
UPDATE MyTable
SET priority = priority - 1
WHERE user = @User
AND priority <= @NewPriority
AND priority > (SELECT priority
FROM MyTable
WHERE user = @User
AND skill = @Skill)
'Set the specified skill's priority as requested.
UPDATE MyTable
SET priority = @NewPriority
WHERE user = @User
AND skill = @Skill