SQL 在 Delete From 语句中带有别名的表变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/597513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Table Variables with an Alias in a Delete From Statement
提问by Anagoge
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 服务器接受表变量的 o1 别名（并且我认为由于“ o1.Month = o2.Month”匹配字段名称而需要别名）。错误是：
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'o1'.
消息 102，级别 15，状态 1，第 11 行
'o1' 附近的语法不正确。
回答by dance2die
Specify the alias name before FROMstatement Meaning, you are deleting from the aliased table.
在FROM语句之前指定别名意思是，您正在从别名表中删除。
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
结果
回答by Joe Pineda
Try this, it ought to work (the first FROM is optional):
试试这个，它应该可以工作（第一个 FROM 是可选的）：
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 可以在它之前。之后，如果您需要执行 JOIN、子查询等，您可以在第二个 FROM 中的表上放置别名。
