How to increment column by one group wise

I have a table called productLocation, and its data structure as follows, SQLFiddle

+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1            | 100         | 0         |
+--------------+-------------+-----------+
| 1            | 101         | 0         |
+--------------+-------------+-----------+
| 1            | 102         | 0         |
+--------------+-------------+-----------+
| 1            | 103         | 2         |
+--------------+-------------+-----------+
| 1            | 104         | 1         |
+--------------+-------------+-----------+
| 1            | 105         | 3         |
+--------------+-------------+-----------+
| 2            | 100         | 0         |
+--------------+-------------+-----------+
| 2            | 101         | 0         |
+--------------+-------------+-----------+
| 2            | 102         | 0         |
+--------------+-------------+-----------+
| 2            | 103         | 1         |
+--------------+-------------+-----------+
| 2            | 104         | 3         |
+--------------+-------------+-----------+
| 2            | 105         | 2         |
+--------------+-------------+-----------+
| 3            | 100         | 0         |
+--------------+-------------+-----------+
| 3            | 101         | 0         |
+--------------+-------------+-----------+
| 3            | 102         | 0         |
+--------------+-------------+-----------+
| 3            | 103         | 1         |
+--------------+-------------+-----------+
| 3            | 104         | 2         |
+--------------+-------------+-----------+

For each location have its own sorting order for products. But some of the products have 0 as the SortValue Now I need to write a query to update SortValue as,

If I consider one location, FkLocationId = 1

+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1            | 100         | 0         |
+--------------+-------------+-----------+
| 1            | 101         | 0         |
+--------------+-------------+-----------+
| 1            | 102         | 0         |
+--------------+-------------+-----------+
| 1            | 103         | 2         |
+--------------+-------------+-----------+
| 1            | 104         | 1         |
+--------------+-------------+-----------+
| 1            | 105         | 3         |
+--------------+-------------+-----------+

In the above data table, you can see, FkProductId = 100,101,102 have 0 as the SortValue. I need to update its sortValue as order by FkProductId descending order. I need to update its like

+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1            | 100         | 3         |
+--------------+-------------+-----------+
| 1            | 101         | 2         |
+--------------+-------------+-----------+
| 1            | 102         | 1         |
+--------------+-------------+-----------+

And also update the previous SortValue by one by one.

Then the complete output should be,

+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1            | 100         | 3         |
+--------------+-------------+-----------+
| 1            | 101         | 2         |
+--------------+-------------+-----------+
| 1            | 102         | 1         |
+--------------+-------------+-----------+
| 1            | 103         | 5         |
+--------------+-------------+-----------+
| 1            | 104         | 4         |
+--------------+-------------+-----------+
| 1            | 105         | 6         |
+--------------+-------------+-----------+

Is this possible to do? I’m really confused about this, please help me to solve this. Thank you

Updated:

Suppose I have another table as product. And its data structure as follows,

+-----------+-----------+
| ProdcutId | SortValue |
+-----------+-----------+
| 100       | 0         |
+-----------+-----------+
| 101       | 0         |
+-----------+-----------+
| 107       | 0         |
+-----------+-----------+
| 108       | 1         |
+-----------+-----------+
| 109       | 2         |
+-----------+-----------+
| 110       | 6         |
+-----------+-----------+
| 111       | 5         |
+-----------+-----------+
| 112       | 4         |
+-----------+-----------+
| 113       | 3         |
+-----------+-----------+

I also need to do same thing for this table as well, How can I do it

Expected Output:

+-----------+-----------+
| ProdcutId | SortValue |
+-----------+-----------+
| 100       | 3         |
+-----------+-----------+
| 101       | 2         |
+-----------+-----------+
| 107       | 1         |
+-----------+-----------+
| 108       | 4         |
+-----------+-----------+
| 109       | 5         |
+-----------+-----------+
| 110       | 9         |
+-----------+-----------+
| 111       | 8         |
+-----------+-----------+
| 112       | 7         |
+-----------+-----------+
| 113       | 6         |
+-----------+-----------+

Answer

Following is a sample query for you to check and use the same logic for your update process using CTE-

DEMO HERE

Important Note: Update is a risky process and you should try with your test data first.

WITH CTE
AS
(
    SELECT *,
    (
        SELECT COUNT(*) 
        FROM #Temp B 
        WHERE B.FkLocationId = A.FkLocationId
        AND SortValue = 0
    ) Zero_Count,
    -- The above Zero count is a simple count of rows with 0
    -- for a specific FkLocationId. This is for adding to other 
    -- rows where there are already value in column SortValue. The logic
    -- is simple, the number of row with 0 in column SortValue, 
    -- should be add to existing value where there is value not equal 0.
    ROW_NUMBER() OVER (PARTITION BY FkLocationId ORDER BY SortValue ASC, FkProductId DESC) RN
    -- ROW_NUMBER is simply creating the Number you should replace 0 by.
    -- As you are looking for 1 to start for the MAX FkProductId with 0,
    -- I applied DESC order on that column
    FROM #Temp A
) 

UPDATE A
SET A.SortValue = 
CASE 
    WHEN A.SortValue = 0 THEN B.RN 
    -- RN is created in such way, so that you can directly 
    -- Replace your value in column SortValue by RN if SortValue = 0 
    ELSE A.SortValue + B.Zero_Count
    -- IF SortValue contains already value > 0, 
    -- You need to just increment the value with Number 
    -- of rows is there with value 0. I have calculated that value
    -- by a sub query in the CTE you can check.
END
FROM  #Temp A
INNER JOIN CTE B ON A.FkLocationId = B.FkLocationId
AND A.FkProductId = B.FkProductId


SELECT * FROM #Temp

Just use your “table_name” where I have used #temp