T-SQL Fixing Row Order / Sequence

Question

Overview:

I have a page in my application that allows a user to set the priority of a list of records. These records are essentially tasks or goals that the team will need to complete. The order they are given is determined by the user dragging and dropping them into place. Once the user is done and saves the changes, it loops over the data and updates the records in the database with their respective order.

Issue:

The problem I am now running into is on a separate page at which the user can delete a record that may have a priority assigned to it. Since the saving and calculation of a priority is done on another page, deleting a record causes gaps in the sequence.

Example:

Projects that are prioritized:

A (1)
B (2)
C (3)
D (4)
E (5)
F (6)
G (7)

Project gets deleted from another page / function:

A (1)
B (2)
C (3)
D (4)
F (6)
G (7)

Question:

I need to make some type of function I can run after a record is deleted to "repair/re sync" this number sequence. Essentially the single column priority needs to be updated and in the example, F would need to be updated to 5 and G updated to 6 in order to fix the sequence gap.

What I have tried:

Mostly researching a way to solve for this to be honest. I was thinking of putting all the records into a temp table with an Auto Increment number and using that to update the priority level but I feel like there is probably a more simple solution and wanted some opinions.

score 1 · Answer 1 · answered Sep 07 '16 at 14:24

After you delete you simple need to update the rows that are greater than the priority just deleted. So if your column name is Priority and you delete the row where Priority = 5 you simply do something like this. Notice you don't need to use loops here.

Update YourTable
set Priority = Priority - 1
where Priority > 5

score 1 · Answer 2 · edited May 23 '17 at 12:16

1

You could use triggers, which would trigger automatically when delete is done. You could always take values from deleted.

More info

SQL Server ON DELETE Trigger

edited May 23 '17 at 12:16

Community

1
1

answered Sep 07 '16 at 14:29

Nagashree Hs

843
6
18

score 1 · Answer 3 · answered Sep 07 '16 at 14:36

As others have suggested, if you're only deleting one at a time, just update the numbers immediately after deletion. If you want to renumber the whole table at once (perhaps there have been multiple deletions), you can use ROW_NUMBER() and a CTE:

DECLARE @Project TABLE (
    Name nvarchar(100)
    , Priority int
);

-- Projects that are prioritized
INSERT @Project ( Name, Priority ) VALUES 
  ('A', 1)
, ('B', 2)
, ('C', 3)
, ('D', 4)
, ('E', 5)
, ('F', 6)
, ('G', 7)
;

SELECT * FROM @Project;

-- Projects get deleted
DELETE @Project WHERE Name = 'E';
DELETE @Project WHERE Name = 'C';

SELECT * FROM @Project;

-- Renumber
WITH P AS
(
    SELECT Name, Priority, ROW_NUMBER() OVER ( ORDER BY Priority ) AS NewPriority
    FROM @Project
)
UPDATE P SET Priority = NewPriority
;

-- Ta da!
SELECT * FROM @Project;

I would think using your CTE renumbering inside a trigger would be safer inside a delete trigger, that way if any other developer/program deletes records from the table it will still maintain the consistency desired by OP — Anthony Hancock, Sep 07 '16 at 17:27

score 0 · Answer 4 · answered Sep 07 '16 at 14:32

Why not just set the Priority in the procedure that extracts the records from the database? that way you don't need to change the priority cause it's always computed on the fly when you read the rows. the only issue is that when you save the new sequence after a user changes the priority sequence, you delete all the records and add them back in in the correct sequence, with an auto-incrementing primary key (pk).

I don't know your sequence, but this example should give you the idea.

Select Record,
  (Select count(*) From table 
   Where pk <= t.pk) Priority
From Table t

T-SQL Fixing Row Order / Sequence

4 Answers4