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