Stored Procedure to update the sort numbers

Question

0.00/5 (No votes)

See more:

HI All

Can someone help me for this please.

I have a column QuesId with question numbers 1,2,3.....10 .
This column is not a primary key.
My Table:

BokID (PK) not Null QuesId Question

1 1 Question1
2 2 Question2
.....
.....
....

10 10 Question10

And if one of the question is deleted then QuesId column should get updated with sequencial numbers.

Many Thanks

Posted 5-Dec-13 23:25pm

babli3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

skydger · Answer 1 · 2013-12-05T23:59:00

Hello.
You should cteate trigger to achieve this goal.
Try this one:

SQL

CREATE TRIGGER [dbo].[trg_DeleteQuestion]
    ON  [dbo].[tasks]
 AFTER DELETE
    AS 
BEGIN
    SET NOCOUNT ON;

    MERGE tasks AS t
    USING (SELECT ROW_NUMBER() OVER(ORDER BY BokID) AS position,
                  tasks.*
                  FROM tasks
         ) AS s
        ON t.BokID = s.BokID
      WHEN MATCHED
      THEN UPDATE SET t.QuesID = s.position;
END

upd
If we have an additional column, then we should write additional condition for it. We should also use DELETED table, which contains deleted row.

SQL

CREATE TRIGGER [dbo].[trg_DeleteQuestion]
    ON  [dbo].[tasks]
 AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

	MERGE tasks AS t
	USING (SELECT ROW_NUMBER() OVER(ORDER BY tasks.BokID, tasks.Stage) AS position,
	              tasks.*
	         FROM tasks
	         JOIN DELETED AS d
	           ON d.Stage = tasks.Stage
	      ) AS s
	   ON t.BokID = s.BokID AND
	      t.Stage = s.Stage
	 WHEN MATCHED
     THEN UPDATE SET t.QuesID = s.position;
END

For more information please see
Triggers[^]
Merge clause[^]
Good luck