How to update single row using many rows from another table 1

[tombos]

I'm using a trigger to keep a quickly accessible sum updated on a master table so that I don't have to query the child table every time I display the total. I want to do this within a trigger on the child table. However, whenever the trigger fires for a multiple-row transaction, i.e. the INSERTED table contains multiple rows, only the first row gets added to my total. Consider...

tblMaster
KeyVal RunningTotal
A 15000
B 22000
C 19000

INSERTED table when my trigger fires
Keyval ThisTransaction
A 1000
B 1500
B 2100
A 3100

After the trigger fires, my tblMaster table should read

A 19,100
B 25,600
C 19,000

Is there a way to perform a single UPDATE statement that performs this rather than grouping into a temporary table and then using that to update the master table? I need this trigger to be as fast as possible.

I realize that I could do

SELECT keyval,sum(thistransaction)
INTO #temp
GROUP BY keyval

then I could use that table to update tblMaster; however, I was hoping for something that would do the summation and update in a single statement.

 

[tlbroadbent]

In the Trigger you could write something like this...

Update tblMaster
Set RunningTotal=RunningTotal + q.TranTot
From tblMaster m
Join
-- Create a subquery to sum the transactions
(Select
KeyVal,
Sum(ThisTransaction) As TranTot
-- Use the inserted virtual table
From inserted
Group By KeyVal) q
On n.KeyVal=q.KeyVal Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.