SQL: too many prefixes error?

"darrel" <notreal@nowhere.com> wrote in message
news:etc4f1$ki7$1@forums.macromedia.com...
> I think my only fear that learning too much T-SQL will frustrate me
> if/when I ever jump ship and go open source. ;o)
>
> -Darrel

Don't worry too much. It doesn't which database you jump from, nor which
to, there will be a period of adjustment. Even in the same database
"family." Kind of like JavaScript syntax is alluringly similar to C++, etc,
but certainly not the same.

>> 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 ...

So, what I'm doing LOOKS like an alias, but it's actually a proprietary
T-SQL command? Man...syntax is hell. ;o)

Thanks, Lionstone!

> 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.

I think my only fear that learning too much T-SQL will frustrate me if/when
I ever jump ship and go open source. ;o)

-Darrel

> 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.

> You'll get an error if you try to use an alias on the target table of a
> delete.

Hmm...I sat down with a DB person here and his opinion is that aliases
should always be used...even in deletes. The problem with my alias statement
was the syntax. I had:

DELETE FROM myTable MYALIAS

when I needed:

DELETE MYALIAS FROM myTable MYALIAS

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.

-Darrel

You'll get an error if you try to use an alias on the target table of a
delete.
It sounds like you might *have* to use the DELETE FROM ... FROM I showed
you. That allows your join to make use of the table aliases while still
targeting the base table properly.


"darrel" <notreal@nowhere.com> wrote in message
news:et9o2s$han$1@forums.macromedia.com...
>> the following should work:
>>
>> DBCourtsPub.CourtsPub.WeFAQs.categoryID)
>
> Nope. I get an error 'DBCourtsPub.CourtsPub.WeFAQs' does not match with a
> table name or alias used in the query.
>
> It seems to be BEGGING me to use an alias. ;o)
>
> -Darrel
>

> the following should work:
>
> DBCourtsPub.CourtsPub.WeFAQs.categoryID)

Nope. I get an error 'DBCourtsPub.CourtsPub.WeFAQs' does not match with a
table name or alias used in the query.

It seems to be BEGGING me to use an alias. ;o)

-Darrel

You should be able to leave off the linked server portion of the table name
from an inner query when you're making a column reference. Normally, the
table name alone would suffice, but I know that there are stricter naming
rules for linked servers (for instance, no implicit resolution to the dbo
schema on object names) and I'm not entirely sure of their full scope. That
being said, the following should work:

DELETE FROM [SQLCRTPUB\COURTSPUBLIC,1892].DBCourtsPub.CourtsPub.WeFAQs
WHERE (categoryID = @categoryID)
OR EXISTS (SELECT * FROM
[SQLCRTPUB\COURTSPUBLIC,1892].DBCourtsPub.CourtsPub.We_about_categories
WHERE parentCategoryID = @categoryID
AND categoryID =
DBCourtsPub.CourtsPub.WeFAQs.categoryID)

You're correct to avoid table aliases in a DELETE query.

> WHERE parentCategoryID = @categoryID AND
> categoryID =
> [SQLCRTPUB\COURTSPUBLIC,1892].DBCourtsPub.CourtsPub.WeFAQs.categoryID)
> ---------------------------
>
> The issue, from what I can tell, is the WHERE clause where I'm tring to
> access the categoryID column.

Ah, well, that is easy to solve...I should just put WHERE categoryID =

However, that leaves the second WHERE statement where I need to reference
the categoryID from 'We_about_categories' and the categoryID from 'WeFAQs'

That's where I'm stumped. Is this a case where I could/should use a table
alias? (From earlier conversations it seems as if aliases should be avoided
in DELETE statements...)

-Darrel