sql-server - 使用双 FROM 子句删除

Question

我很惊讶这能奏效。我不确定我明白为什么。

create table #tempt(something int)

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

我希望在 DELETE 中使用别名，特别是不需要第一个 FROM。为什么第一个 FROM 可以存在？

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

Answer 1

因为如果您查看文档 msdn :

[ 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 
}

有两个FROM!

第一个是可选的，用于标识目标表。

第二个(不是标准的)它可用于通过连接而不是存在来实现对目标表的额外过滤。

经过进一步调查，第一个 FROM 似乎是编写 DELETE FROM 语句的标准方式。但是使用第二个 FROM 会使语句看起来不自然(如您所指出的)，因此这可能是它是可选的背后的原因。