Menu
I need to update a column conditionally and in some cases not update it at all. this is the best way i can figure to do that but it seems like it may be inefficient in the ELSE as it's still updating the column with the current value of the column. Is there a better way in which the ELSE can just "do nothing" at all.
UPDATE mytable
SET col1 = CASE WHEN (col1 = 20) THEN 10 ELSE col1 END
966
The SQL CASE Statement The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
The syntax of the SQL CASE expression is: CASE [expression] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END case_name The CASE statement can be written in a few ways, so let’s take a look at these parameters. The parameters or components of the CASE SQL statement are:
The value used in the ELSE statement is what is returned if no match is found. However, this is an optional part of the SQL CASE statement. If there is no result, and there is no ELSE statement, then the value of NULL is returned. Does The CASE Statement Search All Conditions Or Just Finds The First Match?
case_name (optional): This value indicates what the column should be referred to as when displayed on the screen or from within a subquery. It’s also called the column alias. There are actually two ways to use an SQL CASE statement, which are referred to as a “simple case expression” or a “searched case expression”.
Apply a WHERE:
UPDATE mytable
SET col1 = 10
WHERE col1 = 20
However, if your update is more complex and you actually need multiple CASE you either have to bite the bullet and omit the WHERE or you have to add all columns in the WHERE which you want to update:
UPDATE mytable
SET col1 = CASE WHEN (col1 = 20) THEN 10 ELSE col1 END,
SET col2 = CASE WHEN (col2 = 40) THEN 20 ELSE col2 END,
SET col3 = CASE WHEN (col3 = 80) THEN 30 ELSE col3 END
WHERE col1 = 20 OR col2 = 40 OR col3 = 80
This might still "update" columns unnecessarily(to their old values) but not complete rows.
121
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
