Posted by kalamos on 06/04/05 03:50

The above solutions were great. I only had the "a" in the wrong place. I did
not understand that sql server was "different" from Oracle in this way.

Thanks for all your help.

I'm not really sure what the code means, I'm not the programmer just the
administrator, I got this code second hand. :)

thanks again,
ken.


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns966B6BBFE57FYazorman@127.0.0.1...
> 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]