Delete语句中具有别名的表变量 [英] Table Variables with an Alias in a Delete From Statement

问题描述

我要基于同一表中其他行的存在从SQL Server 2000/2005表变量中删除行（如果存在具有相同日期的非0计数行，则删除所有0计数行）。这是一个简化的示例，只应删除首先添加的行：

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:

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)

问题是我无法让SQL Server接受表变量的o1别名（而且我认为别名是由于匹配字段名称为 o1.Month = o2.Month 而必须）。错误是：

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:

Msg 102，Level 15，State 1，Line 11

Msg 102, Level 15, State 1, Line 11

'o1'附近的语法不正确。

Incorrect syntax near 'o1'.

推荐答案

在前指定别名FROM 语句
表示，您正在从别名表中删除。

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语句中具有别名的表变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！