在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

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

在Delete From语句中带有别名的表变量。

结果

#2

Try this, it ought to work (the first FROM is optional):

试试这个，它应该是有用的(第一个来自可选):

DELETE [FROM] @O
FROM @O o1
where ACount = 0
and exists (select Month from @O o2
      where o1.Month = o2.Month and o2.ACount > 0)

The rationale is: DELETE, as explained here, expects a non-aliased table first, an optional FROM can precede it. After that you do can put an alias on a table in the second FROM, if you need to do a JOIN, subquery, etc.

基本原理是:DELETE，正如这里解释的那样，首先需要一个非别名的表，一个可选的可以先于它。之后，如果需要进行连接、子查询等操作，可以在第二个FROM中向表中添加别名。

#1