表里一记录的值为null,现在想通过下面这种办法让这列不为空:-----------------
id | value
-----------------
1 | NULL
-----------------
2 | abc现在想让id=1 这行值等于id=2的记录的值,也即,让第一行的value=abc,请问这个sql语句或者存储过程该怎么写,谢谢了,在线等!
id | value
-----------------
1 | NULL
-----------------
2 | abc现在想让id=1 这行值等于id=2的记录的值,也即,让第一行的value=abc,请问这个sql语句或者存储过程该怎么写,谢谢了,在线等!
-----------------
insert @t select 1 , NULL
-----------------
union all select 2 , 'abc'
update a
set value=b.value
from @t a,@t b
where a.id=b.id-1
and a.id=1
select * from @t
/*
id value
----------- ----------
1 abc
2 abc(2 行受影响)
*/
declare @t table(id int, value varchar(10))
-----------------
insert @t select 1 , NULL
-----------------
union all select 2 , 'abc'
update a
set value=b.value
from @t a,@t b
where a.id=b.id-1
select * from @t
/*
id value
----------- ----------
1 abc
2 abc(2 行受影响)
*/
declare @t table(id int, value varchar(10))
-----------------
insert @t select 1 , NULL
-----------------
union all select 2 , 'abc'union all select 4 , 'cdf'update a
set value=b.value
from (select px=(select count(1) from @t where id<=t.id),* from @t t) a,
(select px=(select count(1) from @t where id<=t.id),* from @t t) b
where a.px=b.px-1
select * from @t
/*
id value
----------- ----------
1 abc
2 cdf
4 cdf(3 行受影响)
*/
-----------------
insert @t select 1 , NULL
-----------------
union all select 2 , 'abc'union all select 4 , NULLunion all select 6 , 'cdf'union all select 7 , 'ghi'update a
set value=b.value
from (select px=(select count(1) from @t where id<=t.id),* from @t t) a,
(select px=(select count(1) from @t where id<=t.id),* from @t t) b
where a.px=b.px-1
and a.value is null
select * from @t
/*
id value
----------- ----------
1 abc
2 abc
4 cdf
6 cdf
7 ghi(5 行受影响)
*/判断NULL值。
declare @t table(id int, value varchar(10))
-----------------
insert @t select 1 , NULL
union all select 2 , 'abc'
union all select 4 , NULL
union all select 6 , 'cdf'
union all select 7 , 'ghi'update a
set value=(select top 1 value from @t b where b.id>a.id and value is not null)
from @t a where value is null
楼主的意思就是某行value值为null时,就让这个值更新为下一个id的value值
如果下一个id的value值也是空的呢?直到找到不为空的是吗?