[dba-SQLServer] DELETE and LEFT JOIN

Stuart McLachlan stuart at lexacorp.com.pg
Wed May 10 16:45:10 CDT 2006

On 10 May 2006 at 8:25, Susan Harkins wrote:

> This is one of the solutions I came up with -- but thanks for the
> confirmation. 
> Yesterday, I learned that when you use DELETE with any JOIN you MUST use the
> second FROM clause? Anyone heard of this? 

True. That is precisely what TSQL Help says under DELETE when you read it 

Firstly, you need to part way down the page where a "table_source" is 
defined as  one of the following: table_name", "view_name", 
"rowset_function", "derived_table" or "joined_table" 

Also note the restriction on view_name: "The view referenced by view_name 
must be updatable and reference exactly one base table in the FROM clause 
of the view."

Finally look at the part that says:

FROM <table_source> 
Specifies an additional FROM clause. This Transact-SQL extension to DELETE 
allows you to specify data from <table_sources> and delete corresponding 
rows from the table in the first FROM clause. 

Now look at the actual specification of a DELETE command:

In the first clause, optionally preceded by the word "FROM"  - you can have 
a "table_name", "view_name" or "rowset_function_limited". There is no 
mention here of "table_source" or any specification of the last two types 
of  "table_sources", so they are not permitted.

In the  *additional*  FROM clause -  you can have a "table_source"
so this is the *only* place you can have "derived_table" or "join_table"


More information about the dba-SQLServer mailing list