通过循环来update,适合数据量不是很大的:--drop table acreate table A(v datetime)insert into A
select '2012-02-03' union all
select '2012-06-09' union all
select '2012-07-08'
go
--通过循环来update
declare @i int
declare @s intset @i = 1
set @s = (select COUNT(*) from a)while @i <= @s
begin ;with t
as
(
select *,ROW_NUMBER() over(order by getdate()) rownum
from a
) update t
set v = dateadd(day,(select top 1 number from master..spt_values
where type = 'p' and number <=datediff(DAY,'2014-02-14','2014-02-28')+1
order by checksum(NEWID())
),
'2014-02-14')
where rownum = @i set @i = @i + 1
endselect *
from a
/*
v
2014-03-01 00:00:00.000
2014-02-22 00:00:00.000
2014-02-27 00:00:00.000
*/
select '2012-02-03' union all
select '2012-06-09' union all
select '2012-07-08'
go
--通过循环来update
declare @i int
declare @s intset @i = 1
set @s = (select COUNT(*) from a)while @i <= @s
begin ;with t
as
(
select *,ROW_NUMBER() over(order by getdate()) rownum
from a
) update t
set v = dateadd(day,(select top 1 number from master..spt_values
where type = 'p' and number <=datediff(DAY,'2014-02-14','2014-02-28')+1
order by checksum(NEWID())
),
'2014-02-14')
where rownum = @i set @i = @i + 1
endselect *
from a
/*
v
2014-03-01 00:00:00.000
2014-02-22 00:00:00.000
2014-02-27 00:00:00.000
*/
insert into tb select '2012-12-23' union all select '2012-04-28' union all select '2012-06-08'
go
declare @i int,@j int
set @i=1
while @i<4
begin
set @j=floor(15*rand()+1)
update tb set t=dateadd(d,@j,'2014-02-13') where id=@i
set @i=@i+1
end
go
select * from tb
/*
id t
----------- -----------------------
1 2014-02-14 00:00:00.000
2 2014-02-24 00:00:00.000
3 2014-02-27 00:00:00.000(3 行受影响)*/
drop table tb
create table 表A(id int,x varchar(20))insert into 表A(id,x)
select 1,'2012-02-03' union all
select 2,'2012-06-09' union all
select 3,'2012-07-08'
declare @id int
declare ap scroll cursor for
select id from 表Aopen ap
fetch first from ap into @id
while(@@fetch_status<>-1)
begin
update 表A
set x=(select top 1 convert(varchar,dateadd(d,number,'2014-02-14'),23)
from master.dbo.spt_values
where type='P' and number<datediff(d,'2014-02-14','2014-02-28')
order by newid())
where id=@id fetch next from ap into @id
endclose ap
deallocate ap
-- 结果
select id,x from 表A/*
id x
----------- --------------------
1 2014-02-25
2 2014-02-20
3 2014-02-15(3 row(s) affected)
*/