title: Table Variables with an Alias in a Delete From Statement
body: <p>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:</p>
  <pre><code>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 &gt; 0)
  </code></pre>
  <p>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 &quot;<code>o1.Month = o2.Month</code>&quot; matching field names).  The error is:</p>
  <blockquote>
  <p>Msg 102, Level 15, State 1, Line 11</p>
  <p>Incorrect syntax near 'o1'.</p>
  </blockquote>