Posted by Hugo Kornelis on 06/07/05 00:28

On Fri, 3 Jun 2005 22:41:23 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
>> Erland already pointed out that the proprietary UPDATE FROM syntax
>> differs between products. However, why use proprietary code when you can
>> use ANSI-standard code that will work on almost all databases:
>>
>> update ded_temp
>> set balance = (select sum(b.ln_amt)
>> from ded_temp b
>> where ded_temp.cust_no = b.cust_no
>> and ded_temp.ded_type_cd = b.ded_type_cd
>> and ded_temp.chk_no = b.chk_no
>> group by cust_no, ded_type_cd, chk_no)
>
>Could you please explain what this miserable piece of code means? You
>have two ded_temp in the query, so which ded_temp does ded_temp.cust_no
>refer to?
>
>You will have to excuse, but I think it's poor advice to suggest that
>people should use code that is ambiguous. I don't really care if there
>is wording in ANSI that disamguiates the query, it's still bad practice,

Hi Erland,

Thanks for the feedback. As you know, I always welcome comments to the
posts I make in these groups - I'm here to learn as well as to help.

In this case, though, you're missing the point. The update statement
above is completely non-ambiguous, and that has nothing to do with any
wording in ANSI. It has to do with how table aliases work in SQL Server.

If you supply a table with an alias, you can no longer refer to that
table by it's tablename; you can ONLY use the alias. This is easily
demonstrated:

use pubs
select authors.au_fname
from authors a

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'authors' does not match with a table name or alias
name used in the query.

As you see, adding the alias 'a' to the table 'authors' doesn't give a
second name to the set, it changes it's only name. BTW, this behaviour
is (briefly) documented in BOL, index item "FROM clause (descibed)":

(quote from Books Online)
> [AS] table_alias
>
> Is an alias for table_name, view_name, or rowset_function, used either
> for convenience or to distinguish a table or view in a self-join or
> subquery. An alias is often a shortened table name used to refer to
> specific columns of the tables in a join. If the same column name exists
> in more than one table in the join, SQL Server requires that the column
> name must be qualified by a table name or alias. (The table name cannot
> be used if an alias is defined).

Note the parenthesed remark at the end of this paragraph


Back to my update statement. The ded_temp table is used twice. The one
in the subquery is aliased as 'b' - column from this occurence of
ded_temp can never be refered to in the form ded_temp.foo - they MUST be
refered to as b.foo (or just foo - but that _would_ be ambiguous!!). The
one in the UPDATE (outside of the subquery) doesn't have an alias, hence
each column ded_temp.bar can ONLY refer to this occurence of the
ded_temp table.



>because for a human the query is about incomprehensible.

I disagree with that as well. Of course, it IS necessary to always
follow the same conventions. These are my favorites:

1. In a one-table SELECT, UPDATE or DELETE, use no alias and no prefixes
for the column names.
2. In a two-or-more-table SELECT, use aliases for ALL tables and prefix
ALL column names with the appropriatie alias.
3. In a proprietary UPDATE FROM or DELETE FROM, use aliases for ALL
tables and prefix ALL column names with the appropriatie alias (except
the columns on the left of the = symbol in the SET clause). Use the
alias after the UPDATE or DELETE keyword, not the table name. Do not
omit the table to be updated or deleted from the FROM list, even though
SQL Server allows this. And don't use this syntax for single-table
UPDATE or DELETE operations.
4. In an ANSI UPDATE or DELETE with subquery (or subqueries), don't use
an alias for the table to be updated or deleted (not allowed!), but do
use aliases for all tables in the subqueries. Prefix ALL column names
with either the apppropriate prefix, or the tablename of the table to be
updated or deleted.
5. Use ANSI standard constructions, unless there is a good reason to
choose proprietary SQL. Always document the reason for the use of
proprietary SQL.

With the guidelines above, queries such as the one above are far from
incomprehensible. (Though I'd normally use UPPERCASE for all keywords,
CamelCase for table and column names, lowercase for aliases and
different indentation - but I was in a lazy mood when I made my first
post in this thread - too lazy to change the existing capitalization and
layout).


>(And I would
>not be surprised if more than one engine gets lost on the query above,
>so I would not even trust the elusive compatibility.)

Since I know only SQL Server, I'll have to take your word for it.

OTOH, any RDBMS that claims entry-level SQL-92 or above *should* be able
to understand this statement.


>The ANSI standard does not provide a place to put in an alias, and
>that is a serious shortcoming. In this case, it leads to the nonsese
>above. In other cases, imagine that you have a table by the of
>instrumentclearingmarketplaces - writing that over and over again
>is completely out of the question.

The same "problem" occurs with long column names. Using drag and drop
from object editor, copy/paste, programmable macro keys and/or mnemonics
while typing and search&replace when done are easy solutions.


>> BTW, you can also omit the GROUP BY clause, since the subquery will only
>> match rows for one set of (cust_no, ded_type_cd, chk_no) anyway - this
>> might even give you some performance gain!
>
>For some real serious peformance gain, this is likely to be a true
>winner:
>
> UPDATE ded_temp
> SET balance = b.ln_amt
> FROM ded_temp a
> JOIN (SELECT cust_no, ded_type_cd, chk_no, ln_amt = sum(b.ln_amt)
> FROM ded_temp
> GROUP BY cust_no, ded_type_cd, chk_no) AS b
> ON a.cust_no = b.cust_no
> AND a.ded_type_cd = b.ded_type_cd
> AND a.ded_temp.chk_no = b.chk_no
>
>I can give no guarantees, but my experience is that a join with a derived
>table results in a lot more effecient plan, than a correlated subquery.

You are very probably right - I have seen numerous occasions where the
proprietary UPDATE FROM far outperformed the ANSI equivalent. If
performance is the main objective, then UPDATE FROM might be the better
choice. But performance is not always the main objective. If the tables
are small, then nobody will care if it takes 3 ms or 6 ms. If the app
will have to run on multiple platforms (or even if the app is written
with the idea that one day, it *might* be ported), ANSI standard code
should be used unless it'd become a real performance killer.

And finally - if the table to be updated happens to be a view with an
INSTEAD OF trigger, then the proprietary UPDATE FROM syntax can not be
used, so you'll HAVE TO use the ANSI standard equivalent, whether you
like it or not.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]