Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using a conditional UPDATE statement in SQL

Tags:

sql

sql-server

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.

like image 710
Saravanan Avatar asked May 23 '11 13:05

Saravanan


People also ask

How do I update conditional in SQL?

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.

How do you update values in a particular column in SQL with conditions?

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.

What is conditional update?

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.

Can we use subquery in update statement?

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.


2 Answers

I think what you want is:

UPDATE EMPLOYEE SET age = CASE WHEN AGE < 20 THEN 15 ELSE 20 END 
like image 111
JNK Avatar answered Oct 11 '22 10:10

JNK


You can use a case statement in an update as follows...

UPDATE Employee  SET Age = CASE WHEN (age < 20) THEN 15               ELSE 20 END 
like image 30
EBarr Avatar answered Oct 11 '22 11:10

EBarr