Replacing null values

hi!

i need replace null values in 2 columns. in first colum (level1) replace null previous non null value looks table 2 in example below. i,ve tried code on example it,s not working. use 1 variable , loop function on column sql updates variable if theres name not equal null , takes value in variable ant put in te row if row value null.

column 2 more tricky. here use same metod column 1 column 2 should updated previous value column if variable column 1 equal row in column 1. column 2 should in example below. managed in access before i,m trying use sql more need function in sql. sent code access think clarifies better words.

br

structure of file have convert  row	level1	level2  1	test1	  2		testing1  3		  4		  5		  6		  7	test2	testing2  8		  9		  10	test3	  11		  12		  13		  14	test4	testing3  15		  16		    how file should  row	level1	level2  1	test1	  2	test1	testing1  3	test1	testing1  4	test1	testing1  5	test1	testing1  6	test1	testing1  7	test2	testing2  8	test2	testing2  9	test2	testing2  10	test3	  11	test3	  12	test3	  13	test3	  14	test4	testing3  15	test4	testing3  16	test4	testing3    test doesnt work  use test  go  declare @strprevvalue varchar(100);  set @strprevvalue = '';  update tests.dbo.mapconvert2  	set level1 =   	(case isnull(level1, @strprevvalue)  		else @strprevvalue = level1  	end)  group radnr  go      example solution column 1 in access  sqlstmt = "select dokumentavsnitt tbltypkärnansteg1 order sortid asc"    rst.open sqlstmt, currentproject.connection, adopenkeyset, adlockoptimistic        strprevvalue = " "        while rst.eof = false      if isnull(rst![dokumentavsnitt])        rst![dokumentavsnitt] = strprevvalue        rst.update      else        strprevvalue = rst![dokumentavsnitt]      end if        rst.movenext      loop            rst.close    set rst = nothing        me.refresh    goto done    solution column 2 access  sqlstmt = "select dokumentavsnitt, bd tbltypkärnansteg1 order sortid asc"    rst.open sqlstmt, currentproject.connection, adopenkeyset, adlockoptimistic        strprevvalue = ""    strprevlevel = ""        while rst.eof = false      if isnull(rst![bd]) , strprevlevel = rst![dokumentavsnitt]        rst![bd] = strprevvalue        rst.update      elseif isnull(rst![bd])        strprevvalue = ""      else        strprevvalue = rst![bd]        strprevlevel = rst![dokumentavsnitt]      end if        rst.movenext      loop            rst.close    set rst = nothing        me.refresh    goto done            

arne

---

arne olsson

hello, arne

in future, please provide ddl , sample data this:

create table mapconvert2 ( 	row int primary key, 	level1 varchar(50) null, 	level2 varchar(50) null )  insert mapconvert2 values (1, 'test1', null) insert mapconvert2 values (2, null, 'testing1') insert mapconvert2 values (3, null, null) insert mapconvert2 values (4, null, null) insert mapconvert2 values (5, null, null) insert mapconvert2 values (6, null, null) insert mapconvert2 values (7, 'test2', 'testing2') insert mapconvert2 values (8, null, null) insert mapconvert2 values (9, null, null) insert mapconvert2 values (10, 'test3', null) insert mapconvert2 values (11, null, null) insert mapconvert2 values (12, null, null) insert mapconvert2 values (13, null, null) insert mapconvert2 values (14, 'test4', 'testing3') insert mapconvert2 values (15, null, null) insert mapconvert2 values (16, null, null)

to update values, can use this:

update mapconvert2 set level1=( 	select top 1 level1 mapconvert2 b  	where b.row<a.row , b.level1 not null 	order row desc ) mapconvert2 level1 null  update mapconvert2 set level2=( 	select top 1 level2 mapconvert2 b  	where b.row<a.row , b.level2 not null , b.level1=a.level1 	order row desc ) mapconvert2 level2 null 

razvan

SQL Server  >  Transact-SQL