Clever Geek Handbook

Using CASE to Select Column for SET in UPDATE Statement IN SQL SERVER

I want to execute the following instruction. Can I choose which column is updated with CASE?

UPDATE TABVAR CASE 
WHEN M BETWEEN 0 AND 6 THEN SET M0_TO_6 = M
WHEN M BETWEEN 7 AND 18 THEN SET M7_TO_18 = M
WHEN M BETWEEN 19 AND 54 THEN SET M19_TO_54 = M
WHEN M > 54 THEN SET MABOVE54 = M
END
+3


source to share


3 answers


You cannot use an expression case

like this just to return the value (l-). However, you could emulate this behavior with an expression case

for each column:



UPDATE tabvar
SET 
m0_to_6 = CASE WHEN m BETWEEN 0 AND 6 THEN m ELSE m0_to_6 END,
m7_to_18 = CASE WHEN m BETWEEN 7 AND 18 THEN m ELSE m7_to_18 END,
m19_to_54 = CASE WHEN m BETWEEN 19 AND 54 THEN m ELSE m19_to_54 END, 
mabove54 = CASE WHEN m > 54 THEN m ELSE mabove54 END
+3


source


Not so, but you could do basically the same thing:

UPDATE TABVAR
set 
  M0_TO_6 = CASE WHEN M BETWEEN 0 AND 6 THEN M else M0_TO_6 end,
  M7_TO_18 = CASE WHEN M BETWEEN 7 AND 18 THEN M else M7_TO_18 END,
  ...


This way you are updating either the M value in the column or the value that already exists there.

+5


source


I think you want this type of query:

UPDATE TABVAR 
SET M0_TO_6 = CASE WHEN M BETWEEN 0 AND 6 THEN M ELSE M0_TO_6 END,
    M7_TO_18 = CASE WHEN M BETWEEN 7 AND 18 THEN M ELSE M7_TO_18 END,
    M19_TO_54 = CASE WHEN M BETWEEN 19 AND 54 THEN M ELSE M19_TO_54 END,
    MABOVE54 = CASE WHEN M > 54 THEN M ELSE MABOVE54 END
0


source






More articles:


All Articles