SQL Server Performance Forum – Threads Archive
Consolidate mulitiple update statements
HiI have the following scenarion in one of my sp’s. I want to know if there is a way where we can consolidated the following sql statement into one update statement as this is only for one table but with different where clause.
Can we use a case statement ,I tried it but was unable to subsitute the where clause.
here is the query
——————
Update #Table1
Set CurrMONewTotalCredits = isnull((Select sum(CreditsEarned)
From #CreditList ic
Where ic.Name in (‘C1’, ‘C2’, ‘C2’) and ic.DateCreated = @NewDate ),0)
From#Incentive i Update #Table1
SetCurrMORetTotalCredits = isnull((Select sum(CreditsEarned) From #CreditList ic
Where ic.Name in (‘C3’, ‘C43’, ‘C5’, ‘C6’, ‘C5’)
and ic.DateCreated = @NewDate),0)
From#Incentive i Update #Table1
Set CurrMOTotalCredits = CurrMONewTotalCredits+CurrMORetTotalCredits
From#Incentive i Update #Table1
Set CurrYTDNewTotalCredits = isnull((Select sum(CreditsEarned) From #CreditList ic
Where ic.Name in (‘C7’, ‘c8’, ‘C9’)),0)
From#Incentive i
—————–
Thanks in advance Can we use something like this but dosent know how to substitute the where clause update table1
set price =
case when price>10 then price = price*2
when price>20 then price = price*4
else price
Well, the problem is that the criteria for ic.Name are different each time, and in one of the statements you don’t filter for that at all. Plus you’re generating a SUM value everywhere, so it soon becomes rather complicated. I’m pretty sure there is no quicker way than to use the subqueries, but you can combine the first two into a single UPDATE statement: Update #Table1 Set
/*First column*/CurrMONewTotalCredits = isnull((Select sum(CreditsEarned)
From #CreditList ic Where ic.Name in (‘C1’, ‘C2’, ‘C2’) and ic.DateCreated = @NewDate ),0),
/*Second column*/CurrMORetTotalCredits = isnull((Select sum(CreditsEarned) From #CreditList ic
Where ic.Name in (‘C3’, ‘C43’, ‘C5’, ‘C6’, ‘C5’) and ic.DateCreated = @NewDate),0)
From #Incentive i This might or might not help with regards to response time – you’ll just have to test and see. Is this an example with a temp table? If your actual SP does use a temp table, then does the temp table have a PK and proper indexing? For the other two updates I have to wonder why you’re storing the sum of two columns, which is redundant because you can always let SQL Server sum the two columns for you in a query. It also seems a little strange in the last Update to use those name criteria, as they don’t seem to cover the same rows as the previous update statements.
Oops, can you check your table names! If you use a FROM clause in an UPDATE statement, with no alias, then the UPDATE part must use the name mentioned in the FROM clause, with an alias in the FROM clause you must use the alias in the UPDATE part.
Hi ya, a bit confused by the statements, they select from #incentive, update #table1 both without any constraints and then subquery #creditlist? you can get the various values in one select as in select
CurrMONewTotalCredits = sum( case when ic.Name in (‘C1’, ‘C2’, ‘C2’) and ic.DateCreated = @NewDate then CreditsEarned else 0 end ),
CurrMONewTotalCredits = sum( case when ic.Name in (‘C3’, ‘C43’, ‘C5’, ‘C6’, ‘C5’) and ic.DateCreated = @NewDate then CreditsEarned else 0 end ),
CurrYTDNewTotalCredits = sum( case when ic.Name in (‘C7’, ‘c8’, ‘C9’) then CreditsEarned else 0 end )
From
#CreditList ic you could retrieve these into local variables if you then want to go on to update the #incentive table Cheers
Twan
Hi Adriaan
Here is the complete update statement,also if you can provide me with your email i can email you with the complete sp.Thanks for your help and quick response.Also in this sp there are 3 temp tables and a cursor which i want to get rid of to improve the response time.for now check the following to see if we can have a consolidated update statement. Update #Incentive_Calculations
Set
FinalGroup = Case When LocalAssoc = @LocalNum Then ‘Same’
Else ‘Different’
End insert Into #CreditList
Select distinct icl.ReferredByID, CreditsEarned=sum(icl.CreditsEarned), ic.Name, icl.DateCreated
From vwIncentiveCreditTypes ic with (nolock)
Join vwIncentiveCreditLedgers icl with (nolock)
on ic.Id = icl.CreditTypeId
and ic.ProgramId = 1
and icl.DateCreated >= @curr_yr_start
and icl.DateCreated <= @NewDate
Join #Incentive_Calculations i
on icl.ReferredById = i.SpikeId
and i.FinalGroup = ‘Same’
group by icl.ReferredByID, ic.Name, icl.DateCreated
Update #Incentive_Calculations
Set CurrMONewTotalCredits = isnull((Select sum(CreditsEarned) From #CreditList ic
Where ic.Name in (‘CODE1’, ‘CODEA’, ‘CYADJNEW’) and ic.DateCreated = @NewDate
),0)
From#Incentive_Calculations i Update #Incentive_Calculations
SetCurrMORetTotalCredits = isnull((Select sum(CreditsEarned) From #CreditList ic
Where ic.Name in (‘CODE2’, ‘CODE3’, ‘CODE4’, ‘CODEB’, ‘CYADJNEW’) and ic.DateCreated = @NewDate
),0)
From#Incentive_Calculations i Update #Incentive_Calculations
Set CurrMOTotalCredits = CurrMONewTotalCredits+CurrMORetTotalCredits
From#Incentive_Calculations i Update #Incentive_Calculations
Set CurrYTDNewTotalCredits = isnull((Select sum(CreditsEarned) From #CreditList ic
Where ic.Name in (‘CODE1’, ‘CODEA’, ‘CYADJNEW’)),0)
From#Incentive_Calculations i
Update #Incentive_Calculations
SetCurrYTDRetTotalCredits = isnull((Select sum(CreditsEarned) From #CreditList ic
Where ic.Name in (‘CODE2’, ‘CODE3’, ‘CODE4’, ‘CODEB’, ‘CYADJNEW’)),0)
From #Incentive_Calculations i Update #Incentive_Calculations
Set CurrYTDTotalCredits = CurrYTDNewTotalCredits + CurrYTDRetTotalCredits
From#Incentive_Calculations i
Hi Twan
I am trying to update the table this select query is a bit consfusing to me.
Thanks
You could already calculate the SUMs, using Twan’s subqueries, during the INSERT action. Doesn’t look like you would need to run UPDATEs after the fact.
]]>