实例讲解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