[dba-SQLServer] DELETE and LEFT JOIN

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 
carefully:

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"





-- 
Stuart