Menu
I want to execute the following statement. Is it possible to select which column to update using the 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
331
Using CASE in an UPDATE StatementYou can also use CASE in an UPDATE statement. The SQL UPDATE statement is used to change values in an existing table.
CASE statement works like IF-THEN-ELSE statement. I have SQL server Table in which there is column that I wanted to update according to a existing column value that is present in current row. In this scenario, we can use CASE expression. CASE expression is used for selecting or setting a new value from input values.
The CASE expression allows a statement to return one of several possible results, depending on which of several condition tests evaluates to TRUE. You must include at least one WHEN clause within the CASE expression; subsequent WHEN clauses and the ELSE clause are optional.
To do a conditional update depending on whether the current value of a column matches the condition, you can add a WHERE clause which specifies this. The database will first find rows which match the WHERE clause and then only perform updates on those rows.
Not that way, but you could do basically same thing like this:
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're updating either the value M to the column, or the value that already exists in there.
51
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
