Posted by Erland Sommarskog on 06/04/05 01:41

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,
because for a human the query is about incomprehensible. (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.)

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.

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

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]