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'.

score 52 · Accepted Answer · edited Feb 08 '17 at 14:10

52

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

alt text

edited Feb 08 '17 at 14:10

Community

1
1

answered Feb 28 '09 at 05:05

dance2die

35,807
39
131
194

Wow, didn't know about this syntax! Had you not pasted the image, I would've rejected it as invalid. Does this work on SQL2000 or only on SQL Server 2005? I'm at home right now so can't verify directly. – Joe Pineda Feb 28 '09 at 05:28
It should work on SQL 2k/2k5/2k8 (it was tested on 2k8 on the screenshot) - Never dealt with SQL 7 so not sure about 7 – dance2die Feb 28 '09 at 06:34

score 9 · Answer 2 · answered Feb 28 '09 at 05:09

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.

Table Variables with an Alias in a Delete From Statement

2 Answers2

Linked