[dba-SQLServer]updating sequential numbers

Carolina M. Lopez clopez at AirAuto.COM
Thu Dec 11 12:07:45 CST 2003

I have a problem that may be simple, but it's making me pound my head
against the wall.
Here's the scenario:
I have a table with a list of business rules.
This table is numbered in a kind of hierarchy.  The rules have numbers, but
are not used in that order.  One rule number could supersede another due to
some other business constraint.  So, they have another number associated
with them, an id number as the field is named for now at least.
What I need to be able to do is change/update the id field so that this
particular rule's hierarchy is changed either higher or lower, then update
the rest of the id numbers in the table (increment or decrement them by 1)
as necessary.
Keeping the same order, just changing the one.
Am I making this more difficult than it should be?
I probably am....

Table Structure for relevant fields:

id       ruleNumber
1        1001
2        9005
3        1400
4        13
5        1020

Let's say I want to change rule number 1400 to have an id of 5, but keep the
order of the rest...incrementing and decrementing as needed.

More information about the dba-SQLServer mailing list