Search code examples
sql-servert-sqlssmsrow-number

SQL Server: Update ranking on a scheduled basis

The following table is made up of jobs and their respective "ranks":

N   PO      Status
1   110217  Planned
2   110217  Planned
3   108999  Planned
4   108999  Planned
5   108999  Planned
6   108999  Planned
7   110225  Planned
8   110219  Planned
9   110219  Planned
10  110235  Planned
11  110236  Planned
12  110047  Planned
13  110048  Planned

I have a different application that I am using to update the Status column to "completed" once the job is done. I call a stored procedure on a nightly basis to update N to 0 for all jobs that have been completed that day. It looks like this:

N   PO      Status
0   110217  Completed
0   110217  Completed
3   108999  Started
0   108999  Completed
0   108999  Completed
6   108999  Planned
7   110225  Planned
8   110219  Planned
9   110219  Planned
10  110235  Planned
11  110236  Planned
12  110047  Planned
13  110048  Planned

So, originally, the N comes from an insertion from a different table that utilizes a window function. I would like to be able to update the ranking the next day to shift all remaining orders to their new positions. If I run this code here:

SELECT 
ROW_NUMBER() OVER (ORDER BY N) AS N*, *
FROM MyData 
WHERE N <> 0

Then I get my desired result. Which is this:

N*  PO      Status
1   108999  Started
2   108999  Planned
3   110225  Planned
4   110219  Planned
5   110219  Planned
6   110235  Planned
7   110236  Planned
8   110047  Planned
9   110048  Planned

The problem is that I cannot update a table column using a window function. Are there any workarounds for this? Is it possible to mimic the logic of ROW_NUMBER()? A view here is not possible, as the third party application I use does not work with SQL Views.

One last thing note: the dataset here is for one specific category. My data has several categories that all need this same logic to occur, so I would also need the PARTITION BY functionality of ROW_NUMBER()

Solution

  • try the following:

    UPDATE T
SET T.N = T.New_N
FROM 
(
    SELECT N, ROW_NUMBER() OVER (ORDER BY N) AS New_N
    FROM updated_status_table
    WHERE N <> 0
) T

    You can also use CTE to do the update like below:

    ;WITH CTE  AS
(
    SELECT N, ROW_NUMBER() OVER (ORDER BY N) AS RN
    FROM updated_status_table
    WHERE N <> 0
)
UPDATE CTE SET N = RN
FROM CTE

    Please see the db<>fiddle here.

    P.S. You can alwasys add partition by as per your requirement.