I would like to have an UPDATE
statement like this:
SELECT * FROM Employee WHERE age = CASE WHEN (age < 20) THEN age=15 WHEN (age > 20) THEN age= 20
Is this not possible in SQL Server / MySQL? I do not want to use the stored procedures or other things.
Suggest me a suitable way around this problem.
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.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
Conditional update applies to scenarios where high-concurrency applications are updated. In these scenarios, old_value may be updated by other clients. If you use conditional update, the current value is updated to new_value only when the current value is equal to old_value.
UPDATE operations with subqueries that reference the same table object are supported only if all of the following conditions are true: The subquery either returns a single row, or else has no correlated column references. The subquery is in the UPDATE statement WHERE clause, using Condition with Subquery syntax.
I think what you want is:
UPDATE EMPLOYEE SET age = CASE WHEN AGE < 20 THEN 15 ELSE 20 END
You can use a case statement in an update as follows...
UPDATE Employee SET Age = CASE WHEN (age < 20) THEN 15 ELSE 20 END
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