> Hmm...I sat down with a DB person here and his opinion
is that aliases
> should always be used...even in deletes.
No. Deletes should operate on the target table and on the
target table
only.
> The problem with my alias statement was the syntax. I
had:
>
> DELETE FROM myTable MYALIAS
That will fail, as a standard DELETE query does not even
allow a table
alias.
> when I needed:
>
> DELETE MYALIAS FROM myTable MYALIAS
This is actually the T-SQL UPDATE FROM ... FROM extension I
showed you
earlier. The first FROM is always optional. For instance, you
could write
DELETE myTable WHERE something=this.
So above, you really just have DELETE [FROM] myAlias FROM
myTable MyAlias
WHERE ...
> Now, I believe you weren't a fan of Aliases in deletes.
Could you maybe
> explain why you're not a fan of that? While the alias
works, I don't want
> to be using it if it is a bad practice.
While I'm a big fan of the "it works" argument, in this case,
there's no
real cost to do it the safe way. Not all implementations of
SQL will treat
an alias the same way in a query where that alias acts as the
target of some
action (UPDATE, DELETE, ALTER, etc). Some will figure out
what you mean;
some will throw an error; some will perform the action, then
throw it away
because the alias created a temporary object (I've never seen
this behavior
live, but its possibility was drilled into me), etc. I try
not to get into
habits that don't translate well; that's also why I'm careful
to show you a
standard solution whenever I invoke a T-SQL extension. I
think like that,
too, just so I don't fall into bad habits.
I'm a fan of a lot of the T-SQL extensions, but the reason
I'm not shy about
using them is that they're documented, which means they're
supported and
won't be yanked from the next version of SQL Server without
warning. The
SQL Server BOL is clear that the DELETE FROM ... FROM should
target the
actual object in question, and not an alias of that object.
If you do the
opposite, you risk getting unexpected results (sometimes only
when the
optimizer makes certain decisions, too, which is even harder
to nail down),
or having your query break in the next service pack.
The example given in the BOL is this:
-- Transact-SQL extension
USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO
It's a little odd, because UPDATE ... FROM requires the alias
to be the
target of the statement. You'd think that consistency would
be of benefit
here given the other similarities between the two. ;)
Anyway, I'm rambling now. But what it boils down to is that I
never use
undocumented SQL syntax. If syntax is documented, then it's
safe because it
at least won't stop working without warning; anything else
should be
avoided. Working with the web, you're used to
generally-accepted standards
(even if implementation falls short). The SQL standards have
long lagged
behind what databases are actually doing in terms of
features, so they're a
lot more of a mess, and you generally have to rely on your
DB's
documentation as soon as you move past simple queries.
8
Replies
AdChoices