delete statement with additional from clause - deletion from incorrect table sourse - by Vitaliy F

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 436328 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 4/30/2009 1:38:57 AM
Access Restriction Public



Let us examine the following simple delete statement with two from clauses:

delete from t2
from t1 as t2

The surprising result of execution of this query is the deletion of rows from table t1, and not from table t2 as expected.

To see that I'm not mistaken here, I've referred to the MSDN documentation at,

It specifically states that the table in the first from is "the name of the table or view from which the rows are to be removed", and that the second from clause "allows specifying data from <table_source> and deleting the corresponding rows from the table in the first FROM clause."

I wonder why the actual behavior is different?
Is this kind of behavior is by design? And if so, is there a mistake in the documentation?

Many thanks,
Sign in to post a comment.
Posted by Microsoft on 10/26/2011 at 12:24 PM
Hello Vitaliy,
As Steve explained, the behavior you are seeing is by design. We don't have any plans to change the behavior so I am closing this request.

Umachandar, SQL Programmability Team
Posted by Steve Kass on 9/27/2009 at 10:25 AM

I'm afraid I have to disagree with you again. It's an established principle of programming that local definitions of symbols override global definitions. In

delete from t1
from t2 as t1

t1 "suddenly" means something different because you "suddenly" gave it a local meaning. In both queries, you delete from t1. If t1 means something locally, you delete from (local) t1. If t1 does not mean something locally, you delete from (global) t1.

Any other behavior would turn a basic principle of programming (how symbols are resolved - from inner to outer scope) on its head.

When you add the alias clause "as t1", it's just as if you changed
int t1=1, t2=2;
cout << t1;
} // prints 1

to this:

int t1=1, t2=2;
int t1 = t2;
cout << t1;
} // prints 2

You have used the symbol t1 locally, so the local definition is used in the query.

The behavior you prefer would put us in the terrible situation of having queries that use T as an alias change their behavior when someone creates a permanent table named T. Aliases would be useless then.
Posted by Vitaliy F on 5/11/2009 at 1:38 AM
Hi Gail,

The same applies for an update statement.
As I commented below,
I find this behavior to be inconsistent and potentially hazardous.

Posted by Vitaliy F on 5/11/2009 at 1:33 AM
Hello Steve & aaronbertrand ,

I find this behaviour to be really inconsistent.
For instance, when you have a query like:
delete from t1
from t2
The actual table from which the rows are deleted is t1 -
that is in this case, SQL Server treats t1 as an actual data source;
However in the example I've provided above -
delete from t1
from t2 as t1,
t1 is suddenly treated as an alias and not an actual table.

I can't think of a better example for inconsistency than this one..

Posted by Microsoft on 5/8/2009 at 1:04 PM
Hi Vitaliy,
This bug has been assigned to the documentation team. I'll be updating the DELETE topic with a clarification of the behavior and illustrative examples.
Gail Erickson
Posted by AaronBertrand on 4/30/2009 at 3:31 PM
I agree with Steve, if the DELETE were to actually treat your alias as the real table, you'd have a real problem on your hands, and suddenly you would have to be very, very, very careful about how you choose your alias names (or simply stop using them altogether).

I also agree that Books Online is not extremely clear about this, and some clarification is in order. I suggest either changing the title and target of this Connect item, or filing a different one that is aimed at the documentation and not the behavior. I'm finding that I cannot accurately vote for this one as it stands.
Posted by Steve Kass on 4/30/2009 at 2:45 PM
Hi Vitaliy,

This is the correct behavior. Your DELETE statement does not contain any reference to the permanent table t2, only an alias that happens to coincide with a table name. Your statement is identical in meaning to

DELETE FROM ThisIsAnAliasNotATable
FROM t1 AS ThisIsAnAliasNotATable

If you think about it, I think you'll agree that any other behavior would be very bad. If SQL Server produced your "expected behavior," someone could create tables with names used somewhere as aliases, and cause UPDATE and DELETE statements to stop updating the tables they were supposed to update.

When there are two FROM clauses in a DELETE statement, the first FROM clause is interpreted in the context of the table source defined by the second one.

This is exactly how it works with SELECT. This query selects rows from table t1, regardless of whether or not t2 exists.

FROM t1 AS t2

Think of the DELETE statement's second FROM clause this way:

WITH t2 AS (

Books Online could do a better job of explaining this, and I suggest you use the feedback link within Books Online.

Steve Kass