Update subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update subquery

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-01-23 : 15:03:42

Guys

I have the following 2 queries

1st Query
_______________
select account_num, balance from account
order by account_num

2nd Query
_______________
select sum(amount), account_num from atransaction
group by account_num
order by account_num

I want the balance field in account table be update with the value of sum(amount) from the second query. Below update statement doesnt seem to work because of the group by clause.

update account
set balance = sum(a.amount)
from atransaction a inner join account c
on a.account_num = c.account_num
group by a.account_num

Any suggestions how this update statement should be written would help

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-23 : 15:14:46

How about using the following:

update account c
inner join atransaction a on a.account_num = c.account_num
group by a.account_num
set balance = sum(a.amount)

or

update account c set balance = ( Select sum(a.amount) from atransaction a where a.account_num = c.account_num
group by a.account_num)

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-01-23 : 16:15:01

These updates wont work since the update statement will update all the balances to the same value since we cannot use group by clause in the update statement.

The only way for it to make it work is to have something in the where clause that will update only one account at a time with the corresponding balances

Tnanks

imsganesh
Starting Member

28 Posts

Posted - 2006-01-23 : 16:57:55

I hope this will work...you dont need a group by in this subqry...
update account
set balance = (select sum(a.amount)
from account c
where a.account_num = c.account_num
)
from atransaction a

please let me know if this is not working..

Thanks!
SG

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-01-23 : 17:03:04

This wont work either b'cos it will update all the balance values in the account table to the same amount, since there no distinguishing where clause.

Thanks

imsganesh
Starting Member

28 Posts

Posted - 2006-01-23 : 17:05:44

oops sorry...done some mistakes while copying the tables... try this!!

update c
set c.balance = (select sum(a.amount)
from atransaction a
where a.account_num = c.account_num
)
from account c

Thanks!
SG

imsganesh
Starting Member

28 Posts

Posted - 2006-01-23 : 17:15:00

this will work...since there is a join between inner table and the subqry table...try it out!

Thanks!
SG

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-01-23 : 17:28:12

Yes it did work

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-24 : 00:20:33

quote:
Originally posted by Srinika

How about using the following:

update account c
inner join atransaction a on a.account_num = c.account_num
group by a.account_num
set balance = sum(a.amount)

I think this type of join will work in Access
In SQL Server, you need to use

update c
set balance = sum(a.amount)
from account  c inner join atransaction a 
on a.account_num = c.account_num
group by a.account_num

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources