实例讲解SQL Server中"Update"的用法

id_no         in_date 
5791 2003-9-1   14:42:02 
5792 2003-9-1   14:42:02 
5794 2003-9-1   14:42:02 
5795 2003-9-1   14:42:03 
5796 2003-9-1   14:42:03 
5797 2003-9-1   14:42:03 
5831 2003-9-1   14:42:04 
5832 2003-9-1   14:42:14 
5833 2003-9-1   14:42:14

id_no         in_date 
5791 2003-9-1   14:42:02 
5792 2003-9-1   14:42:03 
5794 2003-9-1   14:42:04 
5795 2003-9-1   14:42:05 
5796 2003-9-1   14:42:06 
5797 2003-9-1   14:42:07 
5831 2003-9-1   14:42:08 
5832 2003-9-1   14:42:14 
5833 2003-9-1   14:42:15

--建立测试环境
create table a(id_no varchar(8),in_date datetime)
go
insert into a select '5791','2003-9-1 14:42:02'
union all select '5792','2003-9-1 14:42:02'
union all select '5794','2003-9-1 14:42:02'
union all select '5795','2003-9-1 14:42:03'
union all select '5796','2003-9-1 14:42:03'
union all select '5797','2003-9-1 14:42:03'
union all select '5831','2003-9-1 14:42:04'
union all select '5832','2003-9-1 14:42:04'
union all select '5833','2003-9-1 14:42:04' 
union all select '5734','2003-9-1 14:42:02'
union all select '6792','2003-9-1 14:42:22'
union all select '6794','2003-9-1 14:42:22'
union all select '6795','2003-9-1 14:42:23'
union all select '6796','2003-9-1 14:42:23'
union all select '6797','2003-9-1 14:42:23'
union all select '6831','2003-9-1 14:42:34'
union all select '6832','2003-9-1 14:42:34'
union all select '6833','2003-9-1 14:42:54' 
union all select '6734','2003-9-1 14:42:22'
go
--生成临时表，按照in_date排序
select * into # from a order by in_date
--相同的时间，加一秒。加完了不带重复的
declare @date1 datetime,@date2 datetime,@date datetime
update # 
  set @date=case when @date1=in_date or @date2>=in_date
 then dateadd(s,1,@date2) else in_date end,
      @date1=in_date,
      @date2=@date,
      in_date=@date
--更新到基本表中去
update a set a.in_date=b.in_date from 
a a join # b on a.id_no=b.id_no

select * from a
drop table #,a