I need to Update these NULL Values:
PK | CODE
---+-------
1 | 20
2 | NULL
3 | NULL
4 | 30
5 | NULL
6 | NULL
7 | NULL
8 | 40
9 | NULL
Like this:
PK | CODE
-----+------------
1 | 20
2 | 20
3 | 20
4 | 30
5 | 30
6 | 30
7 | 30
8 | 40
9 | 40
It should always be based on the last minimum value.
I have tried the code below, but it just updates the first row before the one who had value at the beginning.
UPDATE TT
SET CODE = (SELECT CODE
FROM #TSPV_TEMP T2 with(nolock)
WHERE T2.KEY = (tt.KEY -1))
FROM #TSPV_TEMP TT with (nolock)
WHERE tt.CODE IS NULL
You can do this as:
UPDATE TT
SET CODE = (SELECT TOP 1 CODE
FROM #TSPV_TEMP T2 with(nolock)
WHERE T2.KEY < tt.KEY AND
CODE IS NOT NULL
ORDER BY KEY DESC
)
FROM #TSPV_TEMP TT with (nolock)
where tt.CODE IS NULL;
Note the differences in the subquery. This looks for the previous non-NULL value for CODE for the update.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments