Updating multiple columns sql server

e.g if SQL were procedural I would test first and then set the column values.

I was recently working on a project with stored procedures that had a significant amount of column comparisons in a MERGE statement.

This was turning into a real performance bottleneck (relatively speaking) as the entire row of data had to be updated if any one of the numerous fields were modified. Pay particular attention to all the comparisons that were being performed: MERGE [dbo].[The_Table] USING ( SELECT [Account_No], -- rest of columns here FROM [#The_Table Staging] ) b -- the primary keys on the table ON b.[Account_No] = a.[Account_No] AND b.[Transaction_Id] = a.[Transaction_Id] WHEN MATCHEDAND ( --Surely there must be a better, faster way to do this? = COALESCE(a.[Account_Type],'') OR COALESCE(b.[Transaction_Type],'') ! = COALESCE(a.[Transaction_Type],'') OR COALESCE(b.[Branch_Code],'') ! = COALESCE(a.[Branch_Code],'') OR COALESCE(b.[Pay_Method_Cd],'') ! = COALESCE(a.[Pay_Method_Cd],'') OR COALESCE(b.[Trans_Date],'') ! = COALESCE(a.[Trans_Date],'') OR COALESCE(b.[Effective_Date],'') ! = COALESCE(a.[Effective_Date],'') OR COALESCE(b.[Amount], CAST(0 ASDECIMAL(38, 12))) !

= COALESCE(a.[Amount], CAST(0 ASDECIMAL(38, 12))) OR COALESCE(b.[Fund_Code], 0) !

UPDATE tn SET col1 = CASE WHEN condition# = 1 THEN 'a' WHEN condition# = 2 THEN 'j' ...

END, col2 = CASE WHEN condition# = 1 THEN 'b' WHEN condition# = 2 THEN 'z' ...

Leave a Reply