SQL UPDATE syntax to auto rearrange number

Question

I have a very quick question. I have a series of orders where each order can have a priority. The priority can be either NULL or INT. Every now and then I want to arrange the priority back starting from 1.

For example:

NULL, NULL, 5, NULL, 7, NULL, NULL, 15

Change it to

NULL, NULL, 1, NULL, 2, NULL, NULL, 3

What is the most efficient SQL UPDATE syntax to achieve this? Any idea? I couldn't find a good way to archive this but to use cursor.

Cheers, Sam

Edit - as requested by Giorgos Betsos

Schema (simplified for the question purpose)

CREATE TABLE [dbo].[tblOrder]
(
    [OrderId] [int] NOT NULL,
    [Priority] [int] NULL,

    CONSTRAINT [PK_tblOrder] 
        PRIMARY KEY ([OrderId] ASC)
)

Sample output

Order Id | Priority
---------+---------
12343    |  NULL
12344    |  NULL
        ...
        ...
        ...
12449    |  5
12450    |  NULL
12451    |  7
        ...
        ...
        ...
12900    |  NULL
12901    |  NULL
12902    |  15

Thanks!

Answer 1

After clarifications, the query you can use is something like:

;WITH ToUpdate AS (
   SELECT Priority,
          ROW_NUMBER() OVER (ORDER BY Orderid) AS rn
   FROM tblOrder
   WHERE Priority IS NOT NULL
)
UPDATE ToUpdate
SET Priority = rn