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 | +-----------+-----------+
Advertisement
Answer
Following is a sample query for you to check and use the same logic for your update process using CTE-
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