updating a field with sum of fields in another table

Question

I'm having great difficulty in finding how to do this. I've been searching for hours but can't come up with a working solution.

I have two tables.

TableA - Code, TranDate, Amount

TableB - Code Amount

I wish to Update TableB Amount by deducting the sum of Amount in TableA (where a.code = b.code and a.TranDate > 'A GIVEN DATE'

I have to confess to having limited ability with SQL. I am using SQL server 2005.

score 1 · Answer 1 · answered Aug 09 '13 at 15:12

1

Try:

@Date = 'A GIVEN DATE'
UPDATE TableB 
     SET TableB.Amount = TableB.Amount - (SELECT SUM(TableA.Amount) 
                                   FROM TableA as A, TableB 
                                   WHERE A.Code = B.Code AND A.TranDate > @Date)

answered Aug 09 '13 at 15:12

m-oliv

419
11
27

Thanks for the response.I have given this a try. What is happening which has been my main problem all along is that all the records in TableB are updated with the same amount. – user2668352 Aug 12 '13 at 07:55

Abhishek Jain · Answer 2 · 2013-08-09T15:18:22.343

0

Try this:

UPDATE TB 
SET
    TB.Amount = TB.Amount - TotalDeduction
FROM TableB TB
JOIN (SELECT Code, SUM(Amount) AS TotalDeduction FROM TableA GROUP BY Code) TA ON TA.Code = TB.Code

edited Aug 09 '13 at 15:18

answered Aug 09 '13 at 15:09

Abhishek Jain

2,597
1
18
12

Thanks for the response. Couldn't get this to work. Amount appears to get updated with multiple values and update affecting all the records in the table. – user2668352 Aug 12 '13 at 09:52

score 0 · Answer 3 · answered Aug 09 '13 at 15:12

0

declare @date date,
update TableB
set TableB.Amount= TableB.Amount-sum(TableA.Amount) from TableA, TableB
where TableA.code = TableB.code and TableA.TranDate >@date

answered Aug 09 '13 at 15:12

Mahesh Sambu

349
2
15

Thanks for the response. Solution failed with 'aggregate may not appear in the set list of an UPDATE statement – user2668352 Aug 12 '13 at 10:00

dani herrera · Accepted Answer · 2013-08-12T08:34:12.130

0

Easy way with Common Table Extensions:

with cte as (
   select A.Code, sum( A.Amount ) as Amount
   from TableA A inner join TableB B on A.Code = B.Code 
   where A.TranDate > *someDete*
   Group by A.Code
)
update B
Set B.amount = CTE.amount
from TableB B inner join CTE on B.Code = CTE.Code

Edited Due OP comment:

CTE is available from sqlserver 2005: http://technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx

Query is tested on sqlserver 2008: http://sqlfiddle.com/#!3/9e424/1/0

edited Aug 12 '13 at 08:34

answered Aug 09 '13 at 15:14

dani herrera

48,760
8
117
177

Thanks for the response. I am getting 'incorrect syntax near the keyword 'from'. my test code is : with cte as ( select a.code, sum((a.amount) as [amount] from nominald a inner join trial B on a.code = b.code where a.trandate > '2012-08-13' group by a.code ) update b set b.balance = cte.amount from trial b inner join cte on b.code = cte.code – user2668352 Aug 12 '13 at 08:15
Many thanks! Having removed the extra opening bracket Syntax resolved. Solution needed a tweak. For some reason i had to explicitly specify B as the actual table name in the final update statement. Final coding (working) with cte as ( select a.code, sum(a.amount) as amount from nominald a inner join trial B on a.code = b.code where a.trandate > '2012-08-13' group by a.code ) update trial set trial.balance = trial.balance - cte.amount from trial inner join cte on trial.code = cte.code – user2668352 Aug 12 '13 at 09:33

updating a field with sum of fields in another table

4 Answers4