IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB(COL1 VARCHAR(20),COL2 VARCHAR(20),COL3 VARCHAR(20)) INSERT INTO TB (COL1) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 GO DECLARE @TBNAME VARCHAR(100),@SQL VARCHAR(8000) SELECT @TBNAME='TB' SELECT @SQL=ISNULL(@SQL+',','UPDATE '+@TBNAME+' SET ')+NAME+'=''年月日时分秒毫秒''' FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB')EXEC (@SQL) SELECT * FROM TB /* COL1 COL2 COL3 -------------------- -------------------- -------------------- 年月日时分秒毫秒 年月日时分秒毫秒 年月日时分秒毫秒 年月日时分秒毫秒 年月日时分秒毫秒 年月日时分秒毫秒 年月日时分秒毫秒 年月日时分秒毫秒 年月日时分秒毫秒 */
UPDATE TB SET COL1=GETDATE()
--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:[TB] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') AND type in (N'U')) DROP TABLE [TB] GO---->建表 create table [TB]([username] varchar(3),[addtime] datetime) insert [TB] select 'abc','2010-12-20' union all select 'abc','2010-12-20' union all select 'abc','2010-12-20' union all select 'ddd','2010-12-20' union all select 'abc','2010-12-21' union all select 'abc','2010-12-21' union all select 'abc','2010-12-21' union all select 'abc','2010-12-22' union all select 'abc','2010-12-22' union all select 'abc','2010-12-22' union all select 'abc','2010-12-23' union all select 'abc','2010-12-23' union all select 'abc','2010-12-23' GO--> 查询结果DECLARE @I INT=1 UPDATE TB SET [addtime]=DATEADD(SS,@I,addtime),@I=@I+1select * from TB--> 删除表格 --DROP TABLE [TB]
比如这样 orderid就是我要批量更新的字段 id name orderid 1 xxx 2010122009121415 2 yyy 2010122009121415
没看懂 orderid不已经是年月日时分秒了吗,你要更新到毫秒级别吗
是的 我就是要弄成那样 而且orderid不能重复 上面是我想要的效果
if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(3)) insert [tb] select 1,'xxx' union all select 2,'yyy' goalter table tb add orderid bigint; godeclare @bi bigint select @bi=replace(replace(replace(replace(convert(varchar(200),getdate(),121),'-',''),' ',''),':',''),'.','') print @bi update t set t.orderid=@bi+(select count(1) from tb where id<t.id) from tb t goselect * from tb /** id name orderid ----------- ---- -------------------- 1 xxx 20101220133130513 2 yyy 20101220133130514(2 行受影响) **/
GO
CREATE TABLE TB(COL1 VARCHAR(20),COL2 VARCHAR(20),COL3 VARCHAR(20))
INSERT INTO TB (COL1)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
GO
DECLARE @TBNAME VARCHAR(100),@SQL VARCHAR(8000)
SELECT @TBNAME='TB'
SELECT @SQL=ISNULL(@SQL+',','UPDATE '+@TBNAME+' SET ')+NAME+'=''年月日时分秒毫秒''' FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB')EXEC (@SQL)
SELECT * FROM TB
/*
COL1 COL2 COL3
-------------------- -------------------- --------------------
年月日时分秒毫秒 年月日时分秒毫秒 年月日时分秒毫秒
年月日时分秒毫秒 年月日时分秒毫秒 年月日时分秒毫秒
年月日时分秒毫秒 年月日时分秒毫秒 年月日时分秒毫秒
*/
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([username] varchar(3),[addtime] datetime)
insert [TB]
select 'abc','2010-12-20' union all
select 'abc','2010-12-20' union all
select 'abc','2010-12-20' union all
select 'ddd','2010-12-20' union all
select 'abc','2010-12-21' union all
select 'abc','2010-12-21' union all
select 'abc','2010-12-21' union all
select 'abc','2010-12-22' union all
select 'abc','2010-12-22' union all
select 'abc','2010-12-22' union all
select 'abc','2010-12-23' union all
select 'abc','2010-12-23' union all
select 'abc','2010-12-23'
GO--> 查询结果DECLARE @I INT=1
UPDATE TB
SET [addtime]=DATEADD(SS,@I,addtime),@I=@I+1select * from TB--> 删除表格
--DROP TABLE [TB]
id name orderid
1 xxx 2010122009121415
2 yyy 2010122009121415
而且orderid不能重复 上面是我想要的效果
go
create table [tb]([id] int,[name] varchar(3))
insert [tb]
select 1,'xxx' union all
select 2,'yyy'
goalter table tb add orderid bigint;
godeclare @bi bigint
select @bi=replace(replace(replace(replace(convert(varchar(200),getdate(),121),'-',''),' ',''),':',''),'.','')
print @bi
update t
set t.orderid=@bi+(select count(1) from tb where id<t.id)
from tb t
goselect * from tb
/**
id name orderid
----------- ---- --------------------
1 xxx 20101220133130513
2 yyy 20101220133130514(2 行受影响)
**/