34,590
社区成员
发帖
与我相关
我的任务
分享
update #tb2 set id2=t.num from
(select ROW_NUMBER() OVER (order by id2)as num,id from #tb2) as t
where t.id=#tb2.id
[code=SQL]/****** 数据库自动备份并压缩同时删除过期压缩文件 ******/
USE Master
GO
/** 开启xp_cmdshell支持 **/
Exec sp_configure 'show advanced options', 1
Reconfigure with override
Exec sp_configure 'xp_cmdshell', 1
Reconfigure with override
Exec sp_configure 'show advanced options', 0
Reconfigure with override
/** 定义参数 **/
Declare
--备份文件存储路径
@FilePath Nvarchar(100),
--数据库备份格式 为 数据库名称_日期(日期格式:20110326).bak
--数据库名称
@DbName Nvarchar(100),
--数据库备份后缀
@FileName Nvarchar(100),
--被压缩文件名称
@BakFile Nvarchar(100),
--压缩文件名称
@RarFileName Nvarchar(100),
--rar文件存放路劲
@RarFilePath varchar(100),
@RarCmd Nvarchar(150),
@Str varchar(100),
@Dir varchar(100)
Set @FilePath = 'D:\Cr_DataBackup\'
Set @DbName = 'CR_YFTG_FQ12'
Set @FileName = convert(varchar(10),getdate(),112)
Set @RarFilePath = 'C:\Progra~1\WinRAR\RAR.exe'
set @BakFile=@FilePath+@DbName+'_'+@FileName+'.bak'
set @RarFileName=@FilePath+''+@DbName+'_'+@FileName+'.rar'
/** 定义完成 **/
/** 开始完整备份数据库 **/
Set @Str=@FilePath+@DbName+'_'+@FileName+'.bak'
BACKUP DATABASE @DbName TO DISK=@str
WITH RETAINDAYS=15,NOFORMAT,NOINIT,
NAME=N'完整备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10
/** 备份完成 **/
/** 开始压缩新备份文件 **/
Set @RarCmd =@RarFilePath+' a -df -ep1 -m5 '+@RarFileName+' '+@BakFile
Exec master..xp_cmdshell @RarCmd
/** 参数说明 **/
/** a:添加文件到压缩文件 -df:压缩后删除文件 -ep1:从名称中排除基本目录 -m5:压缩级别为最大 **/
/** 压缩完成 **/
/** 删除过期的压缩文件 **/
Set @Dir='Del '+@FilePath+@DbName+'_'+convert(varchar(10),getdate()-4,112)+'.rar'
Exec master..xp_cmdshell @Dir
/** 删除结束 **/
/** 关闭xp_cmdshell支持 **/
Exec sp_configure 'show advanced options', 1
Reconfigure with override
Exec sp_configure 'xp_cmdshell', 1
Reconfigure with override
Exec sp_configure 'show advanced options', 0
Reconfigure with override
/** 过程完成 **/
[/code]
declare @i int
set @i = 0
update tb
set @i = @i + 1,a = @i
/*select * from tb*/
--2005 : row_number
update t
set t.a = b.rn
from tb t,(select rn = row_numbr() over (order by getdate()),* from tb)b
where t.[关联字段] = b.[关联字段]
--2000 : identity
select rn = identity(int,1,1),*
into #t
from tb
update t
set t.a = b.rn
from tb t,#t b
where t.[关联字段] = b.[关联字段]
--上边打错!
declare @i int
set @i = 0
update tb
set @i = @i + 1,a = @i
/*select * from tb*/
--2005 : row_number
update t
set a = t.id
from tb t,(select id = row_numbr() over (order by getdate()),* from tb)b
where t.[关联字段] = b.[关联字段]
--2000 : identity
select rn = identity(int,1,1),*
into #t
from tb
update t
set a = t.id
from tb t,#t b
where t.[关联字段] = b.[关联字段]
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?
update
t
set
a=t.id
from
t,(select id=row_numbr()over(order by getdate()),* from t)b
where
t.关联字段=b.关联字段
;WITH TEMP
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY FID) AS NUM,* FROM TABLE1
)
UPDATE T
SET TB.A=T.NUM
FROM T
INNER JOIN TEMP T1
ON T.ID=T1.ID
不知道数据库及数据库版本·
那就写个oracle的好了·
9i测试通过·
create table A
(
ID NUMBER,
NAME VARCHAR2(10)
);
insert into a values(2,'tom');
insert into a values(3,'tina');
commit;
select * from a;
update a set id = rownum;
commit;
select * from a;