在Delete From语句中带有别名的表变量。
I want to delete rows from a SQL Server 2000/2005 table variable based on the presence of other rows in the same table (delete all 0 count rows if a non-0 count row exists with the same date). Here is a simplified example that should only delete the row added first:
我想基于同一表中其他行存在的情况,从SQL Server 2000/2005表变量中删除行(如果一个非0计数行以相同的日期存在,则删除所有0计数行)。这里有一个简化的示例,应该只删除第一行添加的行:
declare @O table (
Month datetime,
ACount int NULL
)
insert into @O values ('2009-01-01', 0)
insert into @O values ('2009-01-01', 1)
insert into @O values ('2008-01-01', 1)
insert into @O values ('2007-01-01', 0)
delete from @O o1
where ACount = 0
and exists (select Month from @O o2 where o1.Month = o2.Month and o2.ACount > 0)
The problem is that I can't get SQL server to accept the table variable's o1 alias (and I think an alias is required due to the "o1.Month = o2.Month
" matching field names). The error is:
问题是,我无法让SQL server接受表变量的o1别名(我认为,由于“o1”,需要使用别名。月= o2。月”匹配的字段名)。错误的是:
Msg 102, Level 15, State 1, Line 11
Msg 102,第15级,状态1,第11行。
Incorrect syntax near 'o1'.
不正确的语法“o1群”附近。
2 个解决方案
#1
52
Specify the alias name before FROM statement Meaning, you are deleting from the aliased table.
在从语句中指定别名之前,您正在从别名表中删除。
delete o1
from @O as o1
where ACount = 0
and exists ( select Month
from @O o2
where o1.Month = o2.Month
and o2.ACount > 0)
Result
结果
更多相关文章
- ORACLE-SQL:定义并使用多字符串变量
- 在SQL SELECT语句中重用别名字段
- 我不能使用PHP和SQL在循环之间传递变量?
- 每个派生表必须有自己的别名(sql&php)[重复]
- 手把手教你mysql(十五)游标变量流程控制
- 将变量(表名,数据)传递给python mysqld查询
- PLSQL并非所有变量都已绑定
- SQL文结果集起别名
- Android Studio 和 SDK 下载、安装和环境变量配置