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_OPENDAT E is in the same
month as the PORTFOLIO_OPEND ATE, then sum the account_openamt for the
same PRODUCT, and update the CHECKING_OPENAM T, SAVINGS_OPENAMT ,
CD_OPENAMT, etc in table PORTFOLIO. For other accounts opened NOT in
the same month as the PORTFOLIO_OPEND ATE, just ignore them.

--Here are the tables
create table portfolio
(portfolio_id int
,portfolio_open date smalldatetime
,checking_opena mt money
,savings_openam t 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_openda te smalldatetime
,account_openam t money)

insert into account values(1,'check ing','2/15/2005',2000)
insert into account values(1,'check ing','2/20/2005',3000)
insert into account values(1,'savin gs','2/20/2005',3000)
insert into account values(1,'cd',' 5/15/2005',5000)

--Ideal Output--
id portfolio_opend ate checking_openam t savings_openamt cd_openamt
1 2/15/2005 5000 3000 0

--Here is my query:
update portfolio
set checking_openam t=sum(b.account _openamt) --problem appears!
from portfolio as a
join account as b
on a.id=b.id
and year(a.portfoli o_opendate)=yea r(b.account_ope ndate)
and month(a.portfol io_opendate)=mo nth(b.account_o pendate)
and product ='checking'
--and product='saving s'
--and product='cd'

Thanks again!!

[Razvan Socol]

Hi, rola

Your portfolio table SHOULD NOT contain the checking_openam t,
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_ope ndate,
s.checking_open amt, s.savings_opena mt, s.cd_openamt
FROM portfolio p LEFT JOIN (
SELECT portfolio_id,
MONTH(account_o pendate) as account_month,
YEAR(account_op endate) as account_year,
SUM(CASE WHEN product='checki ng' THEN account_openamt ELSE 0 END)
as checking_openam t,
SUM(CASE WHEN product='saving s' 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_o pendate),
YEAR(account_op endate)
) s ON p.portfolio_id= s.portfolio_id
AND MONTH(portfolio _opendate)=acco unt_month
AND YEAR(portfolio_ opendate)=accou nt_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_openam t = a.checkamt,
savings_openamt = a.saveamt,
cd_openamt = a.cdamt
FROM portfolio p
JOIN (SELECT portfolio_id,
month = convert(char(6) , account_opendat e, 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_opendat e, 112)
) AS a ON p.portfolio_id = a.portfolio_id
AND convert(char(6) , p.portfolio_ope ndate, 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****@sommarsk og.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