Update SQL Server table rows with sequential numbers

Question

I have a SQL Server table in the below format and I am looking for a solution to keep the QuestionOrder in the correct sequence for that QuestionnaireID when I delete a record from this table. So let's say I delete the row with ID = 3, the QuestionOrder column for all rows with QuestionnaireID = 1 will be updated to be in the right sequence starting from 1 to N.

ID	QuestionnaireID	Question	QuestionOrder	...
1	1	Question-1	1	...
2	1	Question-2	2	...
3	1	Question-3	3	...
4	1	Question-4	4	...
5	1	Question-5	5	...
6	2	Question-1	1	...
7	2	Question-2	2	...

You may have to parse [Question] if more than 9 questions others you would have 1,10,11,12, and then 2 — John Cappelletti, Nov 01 '21 at 02:17
You don't need to parse [Question], since you already have the `QuestionOrder`, you can simply use `row_number() over (partition by QuestionnaireID order by QuestionOrder)` — Squirrel, Nov 01 '21 at 02:28
You might get some useful ideas from [this](https://stackoverflow.com/a/15858191/92546) or [this](https://stackoverflow.com/questions/48327539/manage-records-ordering/48329605#48329605) answer. — HABO, Nov 01 '21 at 02:28
Aside: This kind of functionality is an example of why I generally limit applications to interacting with a database through stored procedures (SPs). It's a lot easier to write `DeleteQuestion( QuestionnaireId, QuestionId )` and have it do the right thing than to fiddle about with a trigger trying to handle some collection of rows vanishing without knowing the intent. The SP can ensure that there is at least one question left, the order is updated, no outstanding historical data refers to the question, ... . Maintenance is easier with a clean interface between the application and the data. — HABO, Nov 01 '21 at 02:53

score 1 · Accepted Answer · edited Nov 08 '21 at 14:02

Try this, assuming your table's name is QuestTable.

First create the stored procedure:

CREATE PROCEDURE dbo.usp_QuestionOrder_DeleteAndReOrder
    @QuestionnaireID int,
    @QuestionOrder int
AS
BEGIN
    SET NOCOUNT ON;

    DELETE  
    FROM QuestTable 
    WHERE QuestionnaireID = @QuestionnaireID
      AND QuestionOrder = @QuestionOrder;

    UPDATE QuestTable   
    SET QuestionOrder = QuestionOrder - 1
    WHERE QuestionOrder > @QuestionOrder;
END

Then make the call:

DECLARE @QuestionnaireID int = 1,
        @QuestionOrder   int = 3;

EXEC    dbo.usp_QuestionOrder_DeleteAndReOrder 
            @QuestionnaireID, @QuestionOrder;

SELECT * 
FROM QuestTable

replace QuestTable with the name of your table – Andy3B Nov 02 '21 at 20:12 — Andy3B, Nov 02 '21 at 20:12

Update SQL Server table rows with sequential numbers

1 Answers1