if object_id('tempdb..#tmp') is not null
drop table #tmp
GO
----创建测试数据(假设id为主键并且行按照id大小递增排列或id列创建了聚集索引)
declare @t table(id int,nowdatetime datetime)
insert @t
select 1,NULL union all
select 2,NULL union all
select 3,NULL union all
select 4,NULL union all
select 5,NULL----顺次更新,使用日期依次加1作为更新次序标志
declare @nowdatetime datetime
set @nowdatetime = getdate()
update @t set
@nowdatetime = dateadd(dd,1,@nowdatetime),
nowdatetime = @nowdatetime
----查看更新次序
select * from @t ----生成逆向更新的临时表
select * into #tmp from @t order by id desc
----更新临时表
set @nowdatetime = getdate()
update #tmp set
@nowdatetime = dateadd(dd,1,@nowdatetime),
nowdatetime = @nowdatetime
----更新原表(相当于逆向更新)
update a set nowdatetime = b.nowdatetime from @t as a inner join #tmp as b on a.id = b.id
----查看逆向更新
select * from @t ----清除测试环境
drop table #tmp/*顺次更新结果
id nowdatetime
--------------------------------
1 2006-11-06 20:04:13.073
2 2006-11-07 20:04:13.073
3 2006-11-08 20:04:13.073
4 2006-11-09 20:04:13.073
5 2006-11-10 20:04:13.073逆向更新结果
id nowdatetime
--------------------------------
1 2006-11-10 20:04:13.253
2 2006-11-09 20:04:13.253
3 2006-11-08 20:04:13.253
4 2006-11-07 20:04:13.253
5 2006-11-06 20:04:13.253
*/
drop table #tmp
GO
----创建测试数据(假设id为主键并且行按照id大小递增排列或id列创建了聚集索引)
declare @t table(id int,nowdatetime datetime)
insert @t
select 1,NULL union all
select 2,NULL union all
select 3,NULL union all
select 4,NULL union all
select 5,NULL----顺次更新,使用日期依次加1作为更新次序标志
declare @nowdatetime datetime
set @nowdatetime = getdate()
update @t set
@nowdatetime = dateadd(dd,1,@nowdatetime),
nowdatetime = @nowdatetime
----查看更新次序
select * from @t ----生成逆向更新的临时表
select * into #tmp from @t order by id desc
----更新临时表
set @nowdatetime = getdate()
update #tmp set
@nowdatetime = dateadd(dd,1,@nowdatetime),
nowdatetime = @nowdatetime
----更新原表(相当于逆向更新)
update a set nowdatetime = b.nowdatetime from @t as a inner join #tmp as b on a.id = b.id
----查看逆向更新
select * from @t ----清除测试环境
drop table #tmp/*顺次更新结果
id nowdatetime
--------------------------------
1 2006-11-06 20:04:13.073
2 2006-11-07 20:04:13.073
3 2006-11-08 20:04:13.073
4 2006-11-09 20:04:13.073
5 2006-11-10 20:04:13.073逆向更新结果
id nowdatetime
--------------------------------
1 2006-11-10 20:04:13.253
2 2006-11-09 20:04:13.253
3 2006-11-08 20:04:13.253
4 2006-11-07 20:04:13.253
5 2006-11-06 20:04:13.253
*/
if object_id('tempdb..#tmp') is not null
drop table #tmp
GO
----生成逆向更新的临时表
select * into #tmp from tb order by id desc
----更新临时表
update #tmp set nowdatetime = getdate()
----更新原表(相当于逆向更新)
update a set nowdatetime = b.nowdatetime from tb as a inner join #tmp as b on a.id = b.id
----查看逆向更新
select * from tb----清除测试环境
drop table #tmp