生成日期段内的不同日期，update 表 set addtime=啥,解决了立马结

[s208ping]

数据库内有一字段addtime

数据现在有12万条


怎样更新改字段为12万个不重复时间


实现效果：每行的addtime都是唯一的


在此先谢谢大侠们！

[Liyingyue_FFS]

addtime的数据类型是什么？

[liangCK]

DECLARE @start_dt DATETIME,@end_dt DATETIME;
SELECT @start_dt = '2009-01-01',@end_dt = '2009-12-31';

DECLARE @i INT;
SET @i = 0;

UPDATE tb SET 
    addtime = DATEADD(second,@i,@start_dt),
    @i = @i +1;
WHERE addtime < @end_dt;

[you_tube]

DECLARE @start_dt DATETIME,@end_dt DATETIME;
SELECT @start_dt = '2009-01-01',@end_dt = '2009-12-31';

DECLARE @i INT;
SET @i = 0;

WHERE @i < DATEDIFF（second,@start_dt,@end_dt)
BEGIN
UPDATE tb SET 
    addtime = DATEADD(second,@i,@start_dt),
    @i = @i +1;
END 

[you_tube]

modify

DECLARE @start_dt DATETIME,@end_dt DATETIME;
DECLARE @i INT; 
SET @i = 0;  
SELECT @start_dt = '2009-01-01',@end_dt = '2009-12-31'; 

WHERE @i < DATEDIFF (second,@start_dt,@end_dt) 
BEGIN 
 UPDATE tb 
 SET addtime = DATEADD(second,@i,@start_dt), 
  @i = @i +1;
WHERE addtime < @end_dt;

[s208ping]

楼上的谢谢了，、


不过有错误
消息 156，级别 15，状态 1，第 7 行
关键字 'WHERE' 附近有语法错误。

[ks_reny]

 update tb
 set addtime= dateadd(s, RAND(),getdate()) 

[等不到来世]

--如果你的主键是ID的话:
update tb
set addtime=dateadd(s,id,getdate())

[s208ping]

to ks_reny
谢谢

不过随机的可能重复吧

[s208ping]

to szx1999
谢谢

如果执行您的语句，整个字段将是同一个时间

[kingdomxpxp]

嘿嘿~4楼的貌似把While 写成Where了~

[sdhdy]

DECLARE @start_dt DATETIME,@end_dt DATETIME;
SELECT @start_dt = '2009-01-01',@end_dt = '2009-12-31';

DECLARE @i INT;
SET @i = 0;

UPDATE tb SET 
    addtime = DATEADD(second,@i,@start_dt),
    @i = @i +1
WHERE addtime < @end_dt;

改一下2楼的，试试。

[ks_reny]

[Quote=引用 8 楼 s208ping 的回复:]
to ks_reny
谢谢

不过随机的可能重复吧
[/Quote]
那概率小的可以忽略不计。

[sdhdy]

或者：

DECLARE @start_dt DATETIME,@end_dt DATETIME;
SELECT @start_dt = '2009-01-01'
DECLARE @i INT;
SET @i = 0;

UPDATE tb SET 
    addtime = DATEADD(second,@i,@start_dt),
    @i = @i +1

[liangCK]

[Quote=引用 5 楼 s208ping 的回复:]
楼上的谢谢了，、


不过有错误
消息 156，级别 15，状态 1，第 7 行
关键字 'WHERE' 附近有语法错误。
[/Quote]

不好意思..我2楼的多了个分号..去掉就行.

[百年树人]

create table tb(id int,ttime datetime)
insert tb
select 1,null union all
select 2,null union all
select 3,null union all
select 4,null union all
select 5,null union all
select 6,null union all
select 7,null union all
select 9,null union all
select 11,null 

-->更新
declare @stime datetime
set @stime='2009-05-31 00:00:00.000'
update a
set a.ttime=dateadd(ss,b.px,@stime) --每条记录之间间隔1秒钟
from 
  tb a,
  (select *,px=(select count(1)+1 from tb where id<t.id) from tb t) b
where
  a.id=b.id

-->查询
select * from tb

drop table tb

/**
id          ttime                                                  
----------- ------------------------------------------------------ 
1           2009-05-31 00:00:01.000
2           2009-05-31 00:00:02.000
3           2009-05-31 00:00:03.000
4           2009-05-31 00:00:04.000
5           2009-05-31 00:00:05.000
6           2009-05-31 00:00:06.000
7           2009-05-31 00:00:07.000
9           2009-05-31 00:00:08.000
11          2009-05-31 00:00:09.000

（所影响的行数为 9 行）
**/

[ai_li7758521]

DECLARE @i INT
SET @i = 0;
UPDATE tb SET 
    addtime = DATEADD(second,@i,'2009-01-01'),
    @i = @i +1

[等不到来世]

[Quote=引用 9 楼 s208ping 的回复:]
to szx1999
谢谢

如果执行您的语句，整个字段将是同一个时间
[/Quote]
秒不同。

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[col] varchar(4),[addtime] sql_variant)
insert [tb]
select 1,'黄蓉',null union all
select 2,'王蓉',null union all
select 3,'芙蓉',null
go

update tb
set addtime=dateadd(s,id,getdate())

select * from [tb]
/*
id          col  addtime
----------- ---- -------------------------------
1           黄蓉   2009-05-31 11:52:30.400
2           王蓉   2009-05-31 11:52:31.400
3           芙蓉   2009-05-31 11:52:32.400

(3 行受影响)
*/

[JonasFeng]

DECLARE @start_dt DATETIME,@end_dt DATETIME;
SELECT @start_dt = '2009-01-01',@end_dt = '2009-12-31';

DECLARE @i INT 
SET @i = 0 

UPDATE tb SET 
    addtime = DATEADD(second,@i,@start_dt),
    @i = @i +1 
WHERE addtime < @end_dt