MySQL - Using If Then Else in MySQL UPDATE or SELECT Queries

user_235310 0
Viewed 142058

How do I update a table and set different values upon the condition evaluating to True.

For instance :

UPDATE Table
SET A = '1' IF A > 0 AND A < 1
SET A = '2' IF A > 1 AND A < 2
WHERE A IS NOT NULL;

I have seen CASE expression and IF expression in Procedures and Functions but I want to use it in a simple update/select statement. Is it possible or am I expecting too much from this lovely open source database?

mysql
3 Answers
UPDATE table
SET A = IF(A > 0 AND A < 1, 1, IF(A > 1 AND A < 2, 2, A))
WHERE A IS NOT NULL;

you might want to use CEIL() if A is always a floating point value > 0 and <= 2

user_2539840
Related