Posted by Erland Sommarskog on 02/16/06 01:04

rola (rong.guo@gmail.com) writes:
> I am having a problem of using SUM under UPDATE statement. I understand
> that SQL does not allow me to use SUM in UPDATE, but unfortunately, I
> can not find a way to get around it. Can someone please give me some
> idea? Thanks a million!

That's easily done with a derived table:

UPDATE portfolio
SET checking_openamt = a.checkamt,
savings_openamt = a.saveamt,
cd_openamt = a.cdamt
FROM portfolio p
JOIN (SELECT portfolio_id,
month = convert(char(6), account_opendate, 112),
checkamt = SUM(CASE product
WHEN 'checking' THEN account_openamt
ELSE 0
END),
saveamt = SUM(CASE product
WHEN 'savings' THEN account_openamt
ELSE 0
END),
cdamt = SUM(CASE product
WHEN 'cd' THEN account_openamt
ELSE 0
END)
FROM account
GROUP BY portfolio_id, convert(char(6), account_opendate, 112)
) AS a ON p.portfolio_id = a.portfolio_id
AND convert(char(6), p.portfolio_opendate, 112) = a.month

A derived table is a virtual temp table within the query. The
compuation order may be different, this is just a logical way of
express it. This is a very powerful tool to handle complex queries.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]