#linshibiao 临时表结构如下。
id name
1 aa
2 21
3 dsa
4 fg
5 12
6 as
7 ghf
......
我用的是这句想删除所有表中时间为24日的行。就这么做的。、
declare
@i int
set @i=1
while @i<(SELECT COUNT(name) AS Num FROM sysobjects WHERE (xtype = 'u'))
begin
delete from (select name from #linshibiao where _ID=@i )
WHERE _time BETWEEN '2008-7-24 0:01:00' AND '2008-7-25 0:10:12'
set @i=@i+1
end
结果行不通,这句是错的。
delete from (select name from #linshibiao where _ID=@i )
WHERE _time BETWEEN '2008-7-24 0:01:00' AND '2008-7-25 0:10:12'请问该如何实现。
id name
1 aa
2 21
3 dsa
4 fg
5 12
6 as
7 ghf
......
我用的是这句想删除所有表中时间为24日的行。就这么做的。、
declare
@i int
set @i=1
while @i<(SELECT COUNT(name) AS Num FROM sysobjects WHERE (xtype = 'u'))
begin
delete from (select name from #linshibiao where _ID=@i )
WHERE _time BETWEEN '2008-7-24 0:01:00' AND '2008-7-25 0:10:12'
set @i=@i+1
end
结果行不通,这句是错的。
delete from (select name from #linshibiao where _ID=@i )
WHERE _time BETWEEN '2008-7-24 0:01:00' AND '2008-7-25 0:10:12'请问该如何实现。
解决方案 »
- 那里有《数据库系统实现》这本书电子版下载?
- char与nchar的疑惑?
- 问题,高手帮忙~~
- 在存储过程中使用多个游标的问题,在线等候,急!请各位大虾帮忙 !!
- 求两个字段内容相同的记录
- 关于SQL2000中用DTS执行存储过程后,生成数据导出问题
- 求教流水帳的查詢語句
- 如何将Access中的数据一次性存入SQL Server中
- 这个SQL语句怎么执行?
- 如何将一个汉字(vc中unsigned int型)存入表的字段中??
- SELECT TOP 1000 * FROM table;SELECT @@ROWCOUNT 如何用户SqlDataAdapter.fill填充表,并取得@@ROWCOUNT 结果?
- 判断表是否存在
--这样?
delete #linshibiao
WHERE _time BETWEEN '2008-7-24 0:01:00' AND '2008-7-25 0:10:12'
and _ID=@i
这样实现不了我要的效果,我的数据库有2000多个表。。
临时表是我生成的主要是要删除数据库那2000多个表中数据为WHERE _time BETWEEN '2008-7-24 0:01:00' AND '2008-7-25 0:10:12' 的字段
WHERE day(_time )=24
and _ID=@i
#linshibiao
declare @tbname varchar(50)
declare @sql varchar(500)set @i = 1select @tbname = name from #linshibiao where _ID=@i
set @sql ='delete from '+@tbname+' WHERE _time BETWEEN ''2008-7-24 0:01:00'' AND ''2008-7-25 0:10:12'''
exec(@sql)
declare @tbname varchar(50)
declare @sql varchar(500)
set @i=1
while @i<(SELECT COUNT(name) AS Num FROM sysobjects WHERE (xtype = 'u'))
begin
select @tbname=name from #linshibiao where _ID=@i
delete from @tbname WHERE _time BETWEEN '2008-7-24 0:01:00' AND '2008-7-25 0:10:12'
set @i=@i+1
end我是这么写的提示我:必须声明变量 '@tbname'。
例如:EXEC('SELECT * FROM [' + @table + '] WHERE v=''' + @value + '''')
要用动态SQl..........delete from @tbname 有错
where between ...........and
declare @i int
declare @tbname varchar(50)
declare @sql varchar(500)
set @i=1
while @i <(SELECT COUNT(name) AS Num FROM sysobjects WHERE (xtype = 'u'))
begin
select @tbname=name from #linshibiao where _ID=@i
set @sql='delete from '+@tbname+' where _time between "2008-7-24 0:01:00" and "2008-7-25 0:00:10"'
exec(@sql)
set @i=@i+1
end 但提示:
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'XXXX' 无效。是不是dbo用户没有操作权限?那些表是另一个用户创建的,但是怎么用那个用户登陆呢。。设置不了登陆名称。