Adding a field and subtracting the value from another

[Vack58]

Have a table with the following data.

Table1
Ord_no inv_no amt Account freefield3
25 125 100.00 1444
25 125 10.00 3555 Rebate
25 125 10.00 3555 Rebate

After the records are inserted I need to add the amt's where freefield3 equals rebate then subtract that sum from the amt where account = 1444. So my final result would be to have 80.00 in the amt field where account equals 1444

I've tried the following which is subtracting twice so I'm getting 60 instead of 80.

begin
UPDATE gbkmut
SET gbkmut.amt = gbkmut.amt - (SELECT SUM(gbkmut.amt) FROM inserted join gbkmut on
inserted.ord_no = gbkmut.ord_no and inserted.inv_no = gbkmut.inv_no
WHERE inserted.freefield3 = 'Rebate')
WHERE account = ' 1444'
end

 

[RiverGuy]

It looks like you are coding this in a trigger. It can't work the way you want it to. The proper design would probably entail storing line items for the original amount and the rebate amounts, and storing the final amount separately, possibly in a "Header Table."

You need to track the original amount so you can always go back and do your math.