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

Transact-SQL (2005)

Calculations

Author

Topic

theladycoder
Starting Member

16 Posts

Posted - 2008-08-14 : 13:05:24

I am trying to calculate qtyOnHand in one table based on a product number, which is stored in the field prodNum. I want the calculation to be a grand total for each distinct product number, which then updates another table based on product numbers.

If I run the following script the results are what I am looking for:

select sum(qty_on_hand) as TotalQty, prodNum
from table1
where prodNum= prodNum
group by prodNum

Now when I want to take this to an update statement, but what I have tried has not worked. Here are the couple of ways that I have tried:

Update table2
set table2.TxnQty = table1.qty_on_hand
select sum(table1.qty_on_hand) as TotalQty, table1.prodNum
from table1, table2
where table1.prodNum= table2.prodNum

I have even tried an inner join to update this table, but no luck. Any ideas? I am working in SQL 2005. thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 13:41:02

[code]Update t2
set t2.TxnQty =t1.TotalQty
from table2 t2
inner join (select sum(qty_on_hand) as TotalQty, prodNum
from table1
group by prodNum)t1
on t1.prodNum=t2.prodNum[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources