Clever Geek Handbook

Remove using double FROM clause

I'm surprised it works. I'm not sure I understand why.

create table #tempt(something int)

DELETE FROM #tempt -- works fine
FROM #tempt EM

I expect that you will have to use an alias in DELETE, and I would not particularly expect this to be the first FROM

. Why could the former exist FROM

?

DELETE EM --what I would expect to work
FROM #tempt EM
+3


source to share


2 answers


Because if you look at the msdn documentation :

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP (expression ) [ PERCENT ] ] 
    [ FROM ] 
    { { table_alias
      | <object> 
      | rowset_function_limited 
      [ WITH (table_hint_limited [ ...n ] ) ] } 
      | @table_variable
    }
    [ <OUTPUT Clause> ]
    [ FROM table_source [ ,...n ] ] 
    [ WHERE { <search_condition> 
            | { [ CURRENT OF 
                   { { [ GLOBAL ] cursor_name } 
                       | cursor_variable_name 
                   } 
                ]
              }
            } 
    ] 
    [ OPTION ( <Query Hint> [ ,...n ] ) ] 
[; ]    
<object> ::=
{ 
    [ server_name.database_name.schema_name. 
      | database_name. [ schema_name ] . 
      | schema_name.
    ]
    table_or_view_name 
}

There are two FROM

s!



The first is optional and identifies the target table.

The second one (not standard) and can be used to achieve additional filtering on the target table using joins instead of existing ones.

With a little more research, it seems that the former FROM

is the standard way to write instructions DELETE FROM

. But when using the second, the FROM

statement looks unnatural (as you noted), so that might be the reason it is optional.

+2


source


From MSDN (excerpt):

DELETE 
    [ FROM ] 
    { { table_alias
      | table_or_view_name       
    }
    [ FROM table_source [ ,...n ] ]

First FROM

:

FROM: an optional keyword that can be used between the DELETE keyword and target table_name _____ name_

First arguments FROM

:



table_alias: The alias specified in the FROM clause table_source representing the table or view from which rows are to be removed.

table_or view_name: the name of the table or view from which rows are to be removed.

Second FROM

:

FROM table_source: Indicates an optional FROM clause. This Transact-SQL Extension to DELETE allows you to specify data from and delete matching rows from a table in the first FROM clause.

So, in just a few words:

  • The first FROM

    is optional and is used to specify the target table from which rows should be deleted. You can omit that as you point out in the OP and just use the table_alias given in the second FROM

    .
  • The second FROM

    is the T-SQL extension, used to specify the rows to be deleted from the target table.
+2


source






More articles:


All Articles