Update to previous SQL SERVER 2005 value

Question

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 tried the code below, but it just updates the first line before whoever had the value at the beginning.

QUERY

 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 

Answer 1

You can do it like:

 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 searches for a previous value other than NULL for the CODE to update.

Answer 2

 update tbl set code = (select code from tbl x where x.pk = (select max(y.pk) from tbl y where y.pk < tbl.pk and code is not null)) where code is null; 

Violin: http://sqlfiddle.com/#!3/3803d/1/0

Answer 3

Another way is to use a view that, for each pk with a zero code, contains a maximum smaller pk with a non-zero code.

 update t1 set t1.code = t3.code from tt t1 join (select t1.pk, max(t2.pk) max_pk from tt t1 join tt t2 on t1.pk > t2.pk and t2.code is not null and t1.code is null group by t1.pk) t2 on t2.pk = t1.pk join tt t3 on t3.pk = t2.max_pk