Seeding an order field in a table on insert/update

I have a couple tables which have sequential, non-PK integer fields which describes the order of records.  

Example from one table:
101
102
103
104

I have a procedure for this table which handles inserts and updates.  Sometimes the new record will be inserted at the end (max + 1), but sometimes it will be inserted in the middle somewhere.  An updated record may move from one position to another.

What is the most efficient way to re-seed the rest of the record accordingly with the aforementioned operations.  I'll place this at the end of the stored procedure.  

-Paul.
LVL 9
paeloAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You'd be better off avoiding that somehow, but if you really have to do it, UPDATE all records after that by adding 1, for example:

Original rows:
101; 102; 103; 104.

Adding "new" 103::

First do this update:
UPDATE yourTable
SET seqNum = seqNum + 1
WHERE seqNum >= 103

Then insert the new row:
INSERT INTO yourTable (seqNum, ...) VALUES(103, ...)

So the final result will be:

101; 102; +103+; 104(was 103); 105(was 104).
0
 
paeloAuthor Commented:
Thanks for your reply Scott.

This is the type of statement I've been using on an interim basis, but it won't work if I wish to update the order of a current record.  Say I want to move 103 to 105, then the current 104 & 105 have to be reseeded to 103 & 104, respectively.  Or it's possible for 105 to move to 103, and so on.

I have a couple ideas but I'd like to avoid using a cursor to re-seed the list so I'm interested in a solution involving 1 or 2 UPDATE statements.

The problem with this table is that the order needs to be rather arbitrary (not dependent on actual data fields within the table) so I can't think of another solution for having them appear in the proper order.  I'm open to suggestions, however.

Thanks again,
-Paul.
0
 
Scott PletcherSenior DBACommented:
If, rather than adding a row, you are moving a row, qualify the update accordingly:

UPDATE yourTable
SET seqNum = seqNum - 1
WHERE seqNum BETWEEN 104 AND 105

UPDATE yourTable
SET seqNum = 105
WHERE seqNum = 103
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
Btw, I agree, you should definitely avoid a cursor.
0
 
paeloAuthor Commented:
Thanks for your help Scott.

-Paul.
0
 
Scott PletcherSenior DBACommented:
You probably have one already, but just to be sure, create an index on the "sequence number" column if you don't already have one.
0
All Courses

From novice to tech pro — start learning today.