# Updating field in Temporary table

I have a more massive sp, in which I use some temporary tables, below is a part of the sp where I am updating a field in a temporary table two times.

Update #BalSheet
set OBCr = OBDr + isnull(trialDeb.amount,0)
from (
Select v.AccountId,
v.CompanyId,
Sum(isnull(v.Amount,0)) Amount
From #vwJournalDetails v , #TempJournalHeader h
where v.JournalCredit = 1 and // 1
h.JournalId = v.JournalId and
h.CompanyId = v.CompanyId and
h.JournalDate >= @dtPrevFinUnclosedSDate and
h.JournalDate <= @dtLastFinUnclosedEDate
group by v.CompanyId, v.AccountId
) trialDeb
Where trialDeb.Accountid = #BalSheet.AccId and
trialDeb.CompanyId = #BalSheet.CompanyID and
trialDeb.CompanyId = @intCompanyId and
#BalSheet.AcType ='L'

Update #BalSheet
set OBCr = OBDr - isnull(trialDeb.amount,0)
from (
Select v.AccountId,
v.CompanyId,
Sum(isnull(v.Amount,0)) Amount
From #vwJournalDetails v , #TempJournalHeader h
where v.JournalCredit = 0 and // 2
h.JournalId = v.JournalId and
h.CompanyId = v.CompanyId and
h.JournalDate >= @dtPrevFinUnclosedSDate and
h.JournalDate <= @dtLastFinUnclosedEDate
group by v.CompanyId, v.AccountId
) trialDeb
Where trialDeb.Accountid = #BalSheet.AccId and
trialDeb.CompanyId = #BalSheet.CompanyID and
trialDeb.CompanyId = @intCompanyId and
#BalSheet.AcType ='L'


As you can see, the two update query looks similar, and the only difference is in the where condition checking v.JournalCredit value.

If the v.JournalCredit = 1 I add the amount to the current value in the table, and when the v.JournalCredit = 0 I subtract the amount from the current value.

it is working correctly, but there are other update queries similar to this in the sp, So I was wondering if there is any way to mix these update query into one?

You can use a CASE statement in your SET to choose the operation to perform. Not only will this make maintenance easier, it will improve performance as well since you'll only be touching the target table once.

UPDATE  #BalSheet
SET OBCr = CASE
WHEN trialDeb.JournalCredit = 1 THEN OBDr + ISNULL(trialDeb.Amount, 0)
WHEN trialDeb.JournalCredit = 0 THEN OBDr - ISNULL(trialDeb.Amount, 0)
END
FROM  ( SELECT  v.AccountId,
v.CompanyId,
v.JournalCredit,
SUM(ISNULL(v.Amount, 0)) AS Amount
FROM  #vwJournalDetails AS v,
WHERE v.JournalCredit IN ( 0, 1 )
AND h.JournalId = v.JournalId
AND h.CompanyId = v.CompanyId
AND h.JournalDate >= @dtPrevFinUnclosedSDate
AND h.JournalDate <= @dtLastFinUnclosedEDate
GROUP BY v.CompanyId,
v.AccountId,
v.JournalCredit) AS trialDeb
WHERE trialDeb.Accountid = #BalSheet.AccId
AND trialDeb.CompanyId = #BalSheet.CompanyID
AND trialDeb.CompanyId = @intCompanyId
AND #BalSheet.AcType = 'L' ;


Note that I don't know if you still need the v.JournalCredit filter so I just changed it to an IN clause to ensure only 0 & 1 were touched. If those are the only two possible values, you can remove that predicate to improve the performance as well.