Steve Kass : Connect, T-SQLhttp://sqlblog.com/blogs/steve_kass/archive/tags/Connect/T-SQL/default.aspxTags: Connect, T-SQLenCommunityServer 2.1 SP2 (Build: 61129.1)DELETE FROM Where?http://sqlblog.com/blogs/steve_kass/archive/2009/04/30/delete-from-where.aspxThu, 30 Apr 2009 21:48:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:13678skass10http://sqlblog.com/blogs/steve_kass/comments/13678.aspxhttp://sqlblog.com/blogs/steve_kass/commentrss.aspx?PostID=13678<P><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span>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. </SPAN></SPAN></P>
<P><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span>Today, someone was surprised by&nbsp;the basic semantics of DELETE .. FROM.&nbsp;This probably happens a lot, but fortunately today's surprisee&nbsp;posted <A title="Connect item #436328" href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=436328">this Connect item</A> because the following&nbsp;statement didn't do what the documentation seemed to say it would:</SPAN></SPAN></P><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span><FONT color=#0000ff>
<P>DELETE</FONT> <FONT color=#0000ff>FROM</FONT> t2<BR><FONT color=#0000ff>FROM</FONT> t1 <FONT color=#0000ff>AS</FONT> t2</P>
<P></SPAN></SPAN><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span>Quick now, what table does this truncate?</SPAN></SPAN></P>
<P><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span>You're in good company if you said t2, which is the wrong answer. </SPAN></SPAN><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span>The statement truncates t1, however, not t2. In fact, this </SPAN></SPAN><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span>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<BR><BR><FONT color=#0000ff>DELETE</FONT> <FONT color=#0000ff>FROM</FONT> ThisIsAnAliasNotATable<BR><FONT color=#0000ff>FROM</FONT> t1 <FONT color=#0000ff>AS</FONT> ThisIsAnAliasNotATable</P>
<P></SPAN></SPAN><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span>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:</SPAN></SPAN></P><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span><FONT color=#0000ff>
<P>SELECT</FONT> t2<FONT color=#808080>.</FONT>a<BR><FONT color=#0000ff>FROM</FONT> t1 <FONT color=#0000ff>AS</FONT> t2</P>
<P></SPAN></SPAN><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span>If you think about it, you should&nbsp;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. </SPAN></SPAN>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,&nbsp;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:</SPAN></SPAN></P><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span><FONT color=#0000ff>
<P>WITH</FONT> t2 <FONT color=#0000ff>AS </FONT><FONT color=#808080>(<BR>&nbsp; </FONT><FONT color=#0000ff>SELECT</FONT>&nbsp;* <FONT color=#0000ff>FROM</FONT> t1<BR><FONT color=#808080>)<BR>&nbsp; </FONT><FONT color=#0000ff>DELETE</FONT> <FONT color=#0000ff>FROM</FONT> t2</P>
<P>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.</SPAN></SPAN></P>
<P><SPAN style="WIDOWS:2;TEXT-TRANSFORM:none;TEXT-INDENT:0px;BORDER-COLLAPSE:separate;FONT:16px 'Times New Roman';WHITE-SPACE:normal;ORPHANS:2;LETTER-SPACING:normal;WORD-SPACING:0px;-webkit-text-size-adjust:auto;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-stroke-width:0px;" class=Apple-style-span><SPAN style="TEXT-ALIGN:left;FONT-FAMILY:Verdana;FONT-SIZE:11px;" class=Apple-style-span></SPAN></SPAN>&nbsp;</P><img src="http://sqlblog.com/aggbug.aspx?PostID=13678" width="1" height="1">T-SQL language extensionsConnectT-SQLDELETEnonstandard