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

Question

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?

Answer 1

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