October 7, 2009 at 6:23 am
hello experts,
I have two tables:
A
Aid ProductID ProdQty ProdBalanceQty
1 1 100 0
1 2 200 78
1 3 30 10
B
BdetID Bid Aid ProductID Qty
1 1 1 1 50
2 1 1 1 50
3 1 1 2 122
4 1 1 30 20
Now I have an update query
Update A
Set ProdBalanceQty=ProdBalanceQty+Qty
from A a inner join B
on a.Aid=B.Aid and a.ProductID=B.ProductID
where Bid=1
now after the query is fired
A's data is showing like this
Aid ProductID ProdQty ProdBalanceQty
1 1 100 50
1 2 200 200
1 3 30 30
Now for ProductID 2 & 3 correct balance is showing but for
ProductID=1 it is giving wrong value.
I hope i am clear with my query.
Please help me to understand what is happening.
Thanks and Regards.
October 7, 2009 at 6:38 am
Sql doesent work like that , it wont increment the value .
Iternally its not doing a lot different from operating on the results of a select.
You need to do something like this..
Update A
Set ProdBalanceQty=(Select sum(ProdBalanceQty)
from B where a.ProductID=B.ProductID
where Bid = 1)
from A
October 7, 2009 at 7:23 am
@hitendra, providing data like below would get you answers quickly,
Drop table #A
Drop table #B
Create table #A(Aid int, ProductID int, ProdQty int, ProdBalanceQty int)
Create table #B(BdetID int, Bid int, Aid int, ProductID int, Qty int)
INSERT INTO #A VALUES(1, 1, 100, 0)
INSERT INTO #A VALUES(1, 2 ,200, 78)
INSERT INTO #A VALUES(1, 3 ,30, 10)
INSERT INTO #B VALUES(1, 1, 1, 1, 50)
INSERT INTO #B VALUES(2, 1, 1, 1, 50)
INSERT INTO #B VALUES(3, 1, 1, 2, 122)
INSERT INTO #B VALUES(4, 1, 1, 3, 20)
Select * from #A
UPDATE #A
SET ProdBalanceQty = ProdBalanceQty + qty -- select *
From #A a
inner join #B b
on a.Aid=B.Aid and a.ProductID=B.ProductID
where Bid=1
Select * from #A
With the data provided and the query that you are running, it just works fine as we expect it to.....Not sure if I am missing something here. (For table #B last but one value should be 3 i guess and not 30!)
---------------------------------------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply