declare @tb table ( T1 int, T2 varchar(10), T3 varchar(10) ) insert @tb select 1,'A','B' union select 2,'D','3' union select 3,'F','0' --更新 update @tb set T3=isnull((select min(T1) from @tb where T1>t.T1),T3) from @tb t--查看 select * from @tb--结果 /* T1 T2 T3 ----------- ---------- ---------- 1 A 2 2 D 3 3 F 0 */
select identity(int,1,1) as id,* into #temp from table1 update table1 set t3= (select top 1 t1 from #temp where id = table1.t1+1)
(
T1 int,
T2 varchar(10),
T3 varchar(10)
)
insert @tb
select 1,'A','B' union
select 2,'D','3' union
select 3,'F','0' --更新
update @tb
set T3=isnull((select min(T1) from @tb where T1>t.T1),T3)
from @tb t--查看
select * from @tb--结果
/*
T1 T2 T3
----------- ---------- ----------
1 A 2
2 D 3
3 F 0
*/
update table1 set t3=
(select top 1 t1 from #temp where id = table1.t1+1)