Query Help with Update statement, thanks!

[rola]

Hi Group!

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!

--Here is the criteria:
Under the same PORTFOLIO_ID, if the ACCOUNT_OPENDATE is in the same
month as the PORTFOLIO_OPENDATE, then sum the account_openamt for the
same PRODUCT, and update the CHECKING_OPENAMT, SAVINGS_OPENAMT,
CD_OPENAMT, etc in table PORTFOLIO. For other accounts opened NOT in
the same month as the PORTFOLIO_OPENDATE, just ignore them.

--Here are the tables
create table portfolio
(portfolio_id int
,portfolio_opendate smalldatetime
,checking_openamt money
,savings_openamt money
,cd_openamt money)

insert into portfolio values(1,'2/15/2005',0,0,0)

create table account
(portfolio_id int
,product varchar(20)
,account_opendate smalldatetime
,account_openamt money)

insert into account values(1,'checking','2/15/2005',2000)
insert into account values(1,'checking','2/20/2005',3000)
insert into account values(1,'savings','2/20/2005',3000)
insert into account values(1,'cd','5/15/2005',5000)

--Ideal Output--
id portfolio_opendate checking_openamt savings_openamt cd_openamt
1 2/15/2005 5000 3000 0

--Here is my query:
update portfolio
set checking_openamt=sum(b.account_openamt) --problem appears!
from portfolio as a
join account as b
on a.id=b.id
and year(a.portfolio_opendate)=year(b.account_opendate )
and month(a.portfolio_opendate)=month(b.account_openda te)
and product ='checking'
--and product='savings'
--and product='cd'

Thanks again!!

[Razvan Socol]

Hi, rola

Your portfolio table SHOULD NOT contain the checking_openamt,
savings_openamt and cd_openamt columns. These columns should be
computed using a view, like this:

CREATE VIEW portfolio_view AS
SELECT p.portfolio_id, p.portfolio_opendate,
s.checking_openamt, s.savings_openamt, s.cd_openamt
FROM portfolio p LEFT JOIN (
SELECT portfolio_id,
MONTH(account_opendate) as account_month,
YEAR(account_opendate) as account_year,
SUM(CASE WHEN product='checking' THEN account_openamt ELSE 0 END)
as checking_openamt,
SUM(CASE WHEN product='savings' THEN account_openamt ELSE 0 END)
as savings_openamt,
SUM(CASE WHEN product='cd' THEN account_openamt ELSE 0 END)
as cd_openamt
FROM account
GROUP BY portfolio_id, MONTH(account_opendate),
YEAR(account_opendate)
) s ON p.portfolio_id=s.portfolio_id
AND MONTH(portfolio_opendate)=account_month
AND YEAR(portfolio_opendate)=account_year

It is possible to use an UPDATE statement with SUM() in a subquery to
fill these values in the portfolio table, but this should not be done
because it creates redundancy and this leads to possible
inconsistencies. To ensure that you don't have inconsistencies, you can
write the UPDATE-s in a trigger, but you also need to ensure that the
destination columns won't be updated directly. In conclusion, it's best
to do this using a view.

Razvan

[rola]

Thank you so much Razvan! That's a great help!!

[Erland Sommarskog]

rola (ro******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx