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.

]]>