Update with sum inside one table

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 2005 Forums

Transact-SQL (2005)

Update with sum inside one table

Author

Topic

rmi
Starting Member

6 Posts

Posted - 2011-02-21 : 15:58:13

I have table storage:

prod_id, rack, qty, sum_qty
1001, A1, 10, 17
1009, B2, 5, 5
1001, C6, 7, 17

I want to update each row within every transaction to maintain the overall (sum_qty) value. I know how to do this if sum goes to another table but for some reason I didn't find the way to do it in same table.

It something like this I suppose:

UPDATE Storage
SET sum_qty = (SELECT SUM(Qty) FROM Storage GROUP BY Prod_id)

Any help appreciated.

//rmi

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-21 : 18:09:12

summarize the qty column and join the table to itself to update:


update storage set sum_qty = p.num
from prod 
inner join (select prod_id, sum(qty) as num from storage group by prod_id) as p
on storage .prod_id = p.prod_id

Just out of curiosity: why do you keep the redundant total quantity value on each record?

rmi
Starting Member

6 Posts

Posted - 2011-02-22 : 04:38:07

Thanks, I will try that..

I like to calculate 'redundant' values into database because it makes search and listing much easier in my environment..

Is there some reason why I shouldn't do so?

//rmi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources