[dba-SQLServer]updating sequential numbers

Jim Lawrence (AccessD) accessd at shaw.ca
Thu Dec 11 21:28:33 CST 2003


Hi Carolina:

I really hope the id field is not your primary key, if it is you can
not/should not change it. Why not just create another number field like:

Table Structure for relevant fields:

id   ruleId    ruleNumber
1     1   		1001
2     2   		9005
3     3   		1400
4     4   		13
5     5   		1020
6	6		1200
7	7		123

Run a SP that
First saves the position of the ruleID you want to change and the value you
want to change it to. ie. id 6 change ruleID to 3.

Second run the following run an update query that is something like:

update rules SET ruleId = ruleId + (ruleID >= 3 and rulID <= 6);

Given that true = 1 and False = 0 then
the values all stay the same below the value 3, increment if greater or
equal to three but less than or equal to 6. A gap opens in the third
position and the ruleId continues to increment until the sixth position 6 is
overwriten by 7. Like this

id   ruleId    ruleNumber
1     1   		1001
2     2   		9005
3     4   		1400
4     5   		13
5     6   		1020
6	7		1200
7	7		123

Then change ruleID in the position 6 to 3

update rules SET ruleId = 3 where id = 6;

id   ruleId    ruleNumber
1     1   		1001
2     2   		9005
3     4   		1400
4     5   		13
5     6   		1020
6	3		1200
7	7		123

and because ruleID is an index the list will look like this.

id   ruleId    ruleNumber
1     1   		1001
2     2   		9005
6	3		1200
3     4   		1400
4     5   		13
5     6   		1020
7	7		123

HTH
Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Carolina
M. Lopez
Sent: Thursday, December 11, 2003 10:08 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer]updating sequential numbers


Greetings!
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.

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



More information about the dba-SQLServer mailing list