Delete statement with variable after DELETE

Can someone please explain what this does? I'm referring to the @comps straight after delete - the docs seem to suggest it places the deleted rows into @comps, is this true? @comps is a table variable.

de开发者_如何学编程lete @comps
from @comps as a
where a.compcode = '43423'

---

You are reading the docs wrong. To put the deleted rows into a table variable you would use the OUTPUT clause (see example g in BOL for that)

The relevant part of the syntax here is

DELETE 
    [ FROM ] 
    { <object> | rowset_function_limited 
    [ FROM <table_source> [ ,...n ] ] 

Your query is missing the first optional FROM so the first reference to @comps is the target table for deletion. The second (aliased reference) is documented in BOL as follows

FROM <table_source>

Specifies an additional FROM clause. This Transact-SQL extension to DELETE allows specifying data from and deleting the corresponding rows from the table in the first FROM clause.

This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.

In terms of explaining what is happening in your query however the documentation seems to be lacking. A self join gets treated differently from a join onto a different table.

declare @comps table (compcode char(5))
declare @T table (compcode char(5))

INSERT INTO @comps VALUES ('12345')
INSERT INTO @comps VALUES ('43423')

/*Copy the table*/
insert into @T
select * from @comps

delete @comps
from @T as a
where a.compcode = '43423'

select * from @comps /*The table is empty. For every row in @comps the 
                       join on a.compcode = '43423' returns a match so
                       all rows get deleted*/



/*Add back the deleted rows*/
INSERT INTO @comps VALUES ('12345')
INSERT INTO @comps VALUES ('43423')

/*Try the self join*/
delete @comps
from @comps as a
where a.compcode = '43423'

SELECT * FROM @comps /*Returns 12345*/

The plans are below

---

Yeah, you are right. In this code, "@comps" can only be a table variable.

You can also write

delete from @comps where compcode = '43423'

It's totally equivalent

---

@comps and a is the same table referenced with different aliases.

This is also valid and do the exact same thing.

delete a
from @comps as a
where a.compcode = '43423'

The fact that it is the same is best show with this sample

delete @comps
from @comps as a
  inner join @comps as b
    on a.compcode = b.compcode
where a.compcode = '43423'

This will give you error

Msg 8154, Level 16, State 1, Line 9
The table '@comps' is ambiguous.

SQL Server does not know which @comps to delete from, a or b.

Edit 1 I see now that this is probably more of a comment to Martin's post. Not an answer to the OP about what is in the docs.