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!

Can you please also post table schema and adjust your sample data so as to include the field names? — Giorgos Betsos, Jan 14 '17 at 16:39
Don't avoid using cursors just because everyone has told you they are "evil". If you know the syntax and can write the query you need, go ahead and use them. I realise you are asking "What is the most efficient SQL UPDATE.." but you never know, the cursor could perform just fine. :) — Tony, Jan 14 '17 at 16:54
@Tony Cursors are not only inefficient and cumbersome to use, they are imperative and orthogonal to the declarative set-based thinking which is native to relational databases. Once you really understand what set-based means you'll never look back to cursors unless you are forced to use them. — Lucero, Jan 14 '17 at 16:58
@Lucero I'm well aware of set based queries, and I prefer to use them myself. All I was saying to Sam is that if you have the knowledge to write the query needed, go for it. It may well solve the problem they have without having to wait for an alternative solution. — Tony, Jan 14 '17 at 17:42

Giorgos Betsos · Accepted Answer · 2017-01-14T16:50:24.213

3

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

edited Jan 14 '17 at 16:50

answered Jan 14 '17 at 16:46

Giorgos Betsos

71,379
9
63
98

2

`mycol <> NULL` should be `mycol IS NOT NULL` – Pரதீப் Jan 14 '17 at 16:47
@Giorgos Betsos, WOW, this is really cool! I am just wondering, can I do (ORDER BY Priority) instead of (ORDER BY Orderid)? Because the priority doesn't necessary linked to OrderId. The user can set priority number on any order. – Sam Jan 14 '17 at 16:56
@Sam Yes you can use any table field you like in the `ORDER BY` of `ROW_NUMBER`. – Giorgos Betsos Jan 14 '17 at 16:57

SQL UPDATE syntax to auto rearrange number

1 Answers1