THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Steve Kass

SQL MVP since 2002 Professor of Mathematics and Computer Science at Drew University

DELETE FROM Where?

For years, SQL Server has supported a (second) FROM clause in UPDATE and DELETE statements. Its behavior isn't always deterministic, a fact Microsoft points out in the documentation.

Today, someone was surprised by the basic semantics of DELETE .. FROM. This probably happens a lot, but fortunately today's surprisee posted this Connect item because the following statement didn't do what the documentation seemed to say it would:

DELETE FROM t2
FROM t1 AS t2

Quick now, what table does this truncate?

You're in good company if you said t2, which is the wrong answer. The statement truncates t1, however, not t2. In fact, this DELETE statement contains no reference to the table t2, assuming there even is one. Here, t2 is only an alias, and the statement is semantically identical to

DELETE FROM ThisIsAnAliasNotATable
FROM t1 AS ThisIsAnAliasNotATable

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, which is analogous to how SELECT works. The following query selects rows from t1, not t2, and that's no surprise:

SELECT t2.a
FROM t1 AS t2

If you think about it, you should agree that different behavior for DELETE would be very bad. If SQL Server produced what Vitaliy expected, 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. What makes the DELETE behavior more confusing than the SELECT behavior is the fact that in the case of DELETE, the keyword FROM can be used twice. It you write DELETE .. FROM statements with two FROMs, remember that the bottom FROM clause is the "outer" one in the sense of scope. It should be considered first, as is clearer in this DELETE statement, which is equivalent to the one that suprised Vitaliy:

WITH t2 AS (
 
SELECT * FROM t1
)
 
DELETE FROM t2

For the record, this CTE-based DELETE is just as non-standard as the DELETE .. FROM, but it's less confusing. If you can't easily (or effeciently) avoid the use of DELETE .. FROM, consider rewriting DELETE .. FROM as a CTE to reduce the chance of confusion.

 

Published Thursday, April 30, 2009 6:48 PM by skass

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

dp said:

Interesting, but I'd fire anyone that actually wrote such a statement:

DELETE FROM t2

FROM t1 AS t2

April 30, 2009 10:28 PM
 

Arjan Fraaij said:

Why is this wrong, it does exactly what the statements says or not?

DELETE FROM t2

FROM t1 AS t2

this is simple just the same as saying:

DELETE FROM t1

Why indeed whould you write someting like this?

The first FROM is optional and you would only put in a second from if you would like to limit the deletes, so it could have been usefull when a where clause was provided something like:

DELETE FROM t2

FROM t1 AS t2

WHERE t2.id > 100

Alto also this is strange because it's the same as:

DELETE FROM t1 WHERE id > 100

The second from is more used for putting in a join for deletion or a subquery

DELETE FROM t2

FROM (SELECT top 10 id FROM t1) as t2

WHERE t1.id = t2.id

or am i wrong?

May 1, 2009 4:49 AM
 

Paul White said:

A fun article!

I have never liked the DELETE FROM <table> or INSERT INTO <table> form.  Personally, I prefer DELETE <table> and INSERT <table>.  YMMV of course.

Omitting the optional keyword would make the first example:

DELETE  t2

FROM    t1 AS t2;

Which seems clearer to me...?

Paul

May 1, 2009 10:18 AM
 

Alexander Kuznetsov said:

I think this is misleading - shouldn't it be deprecated or dropped completely?

May 1, 2009 11:27 AM
 

Noel McKinney said:

Thank goodness the UPDATE syntax does not include an unnecessary preposition similar to INSERT INTO.  Imagine writing something like UPDATE ON <table> SET...

The optional second FROM clause doesn't seem add anything but confusion, I think Alexander's suggestion to deprecate it is quite interesting.

May 3, 2009 9:16 AM
 

S Harvey said:

If someone wrote

DELETE FROM t2

FROM t1 AS t2

at my company, I'd slap them silly. I hate it when people add create aliases for absolutely no reason

:-)

May 3, 2009 10:58 AM
 

D Yoder said:

WTF are you talking about?  

May 3, 2009 12:54 PM
 

ALZDBA said:

To my understanding it is the first from clause that is optional.

Delete T2

from mytable_1 T1

inner join mytable_2 T2

on T2.FKx = T1.Id

Did anyone check the performance impact of ...

Delete mytable_2

from mytable_1

inner join mytable_2

on mytable_2.FKx = mytable_1.Id

As I recall the first statement would outperform the second.

(I didn't re-evaluate it with sql2008)

Anyway I advise to use aliasses if more than one table is involved in the statement.

May 5, 2009 4:59 AM
 

Hugo Kornelis said:

Alexander: I agree completely. In fact, I submitted a suggestion on Connect and blogged about it here quite some time ago to depreacte both UPDATE ... FROM and DELETE ... FROM.

By the way, the reason for this behaviour is that, as long as UPDATE FROM and DELETE FROM are accepted, it is sometimes necessary to use an alias. Imagine the following UPDATE FROM that is based on a self-join:

UPDATE     MyTable  --- Which of the two does this refer to?

SET        Column1 = parent.Column2

FROM       MyTable AS child

INNER JOIN MyTable AS parent

     ON   parent.ID = child.ParentID;

This will rightly result in an error. The only correct way to write it would be:

UPDATE     child --- NOW we know which one has to be updated!

SET        Column1 = parent.Column2

FROM       MyTable AS child

INNER JOIN MyTable AS parent

     ON   parent.ID = child.ParentID;

And the same argument goes for DELETE FROM (though I have in fact not once encountered a case where a DELETE FROM could not be written as a standard DELETE WHERE EXISTS without any performance loss).

May 5, 2009 5:03 AM
 

Alexander Kuznetsov said:

Hi Hugo,

Where is the Connect item to vote for?

May 5, 2009 10:22 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement