Table Variables with an Alias in a Delete From Statement

Question

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) 

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

Incorrect syntax near 'o1'.

Answer 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