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  gt  0 AND A  lt  1 SET A    2  IF A  gt  1 AND A  lt  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

User · Answer

Here s a query to update a table based on a comparison of another table  If record is not found in tableB  it will update the  active  value to  n   If it s found  will set the value to NULL  UPDATE tableA LEFT JOIN tableB ON tableA id   tableB id SET active   IF tableB id IS NULL   n   NULL      Hope this helps someone else

User · Answer

Whilst you certainly can use MySQL s IF   control flow function as demonstrated by dbemerlin s answer  I suspect it might be a little clearer to the reader  i e  yourself  and any future developers who might pick up your code in the future  to use a CASE expression instead   UPDATE Table SET    A   CASE          WHEN A  gt  0 AND A  lt  1 THEN 1          WHEN A  gt  1 AND A  lt  2 THEN 2          ELSE A        END WHERE  A IS NOT NULL   Of course  in this specific example it s a little wasteful to set A to itself in the ELSE clause   better entirely to filter such conditions from the UPDATE  via the WHERE clause   UPDATE Table SET    A   CASE          WHEN A  gt  0 AND A  lt  1 THEN 1          WHEN A  gt  1 AND A  lt  2 THEN 2        END WHERE   A  gt  0 AND A  lt  1  OR  A  gt  1 AND A  lt  2     The inequalities entail A IS NOT NULL    Or  if you want the intervals to be closed rather than open  note that this would set values of 0 to 1   if that is undesirable  one could explicitly filter such cases in the WHERE clause  or else add a higher precedence WHEN condition    UPDATE Table SET    A   CASE          WHEN A BETWEEN 0 AND 1 THEN 1          WHEN A BETWEEN 1 AND 2 THEN 2        END WHERE  A BETWEEN 0 AND 2   Though  as dbmerlin also pointed out  for this specific situation you could consider using CEIL   instead   UPDATE Table SET A   CEIL A  WHERE A BETWEEN 0 AND 2

User · Answer

UPDATE table SET A   IF A  gt  0 AND A  lt  1  1  IF A  gt  1 AND A  lt  2  2  A   WHERE A IS NOT NULL    you might want to use CEIL   if A is always a floating point value  gt  0 and  lt   2

[mysql] MySQL - Using If Then Else in MySQL UPDATE or SELECT Queries

Examples related to mysql