Multiple column updates in one statement – Microsoft SQL Server

I am trying to update three columns depending on three different conditions in Microsoft SQL Server. See the following example for details

UPDATE s 
SET s.Column1 = NULL 
FROM #My_Temp_Table s  
WHERE ISNUMERIC(ISNULL(S.Column1, 0)) = 0 
     
UPDATE s 
SET s.Column2 = NULL 
FROM #My_Temp_Table s  
WHERE ISNUMERIC(ISNULL(S.Column2, 0)) = 0 

UPDATE s 
SET s.Column3 = NULL 
FROM #My_Temp_Table s  
WHERE ISNUMERIC(ISNULL(S.Column3, 0)) = 0 

How would I refactor the query if I want to do it in one update statement? Appreciate your help!

Answer

You don’t really need the where clauses. And we can take advantage of the fact that TRY_CONVERT() will yield NULL if the value can’t be converted to the target type. (I’m guessing you want to make sure they’re integers, but you can replace int below with any numeric type.)

UPDATE #My_Temp_Table 
  SET Column1 = TRY_CONVERT(int, Column1),
      Column2 = TRY_CONVERT(int, Column2),
      Column3 = TRY_CONVERT(int, Column3);

You can add the WHERE clauses, but since you need to use OR and return any row that matches any of the three criteria, you’re likely better off just scanning once as above:

UPDATE #My_Temp_Table 
  SET Column1 = TRY_CONVERT(int, Column1),
      Column2 = TRY_CONVERT(int, Column2),
      Column3 = TRY_CONVERT(int, Column3)
WHERE TRY_CONVERT(int, Column1) IS NULL
   OR TRY_CONVERT(int, Column2) IS NULL
   OR TRY_CONVERT(int, Column3) IS NULL;