Technology news and views
RSS icon Email icon Home icon
  • Understanding MySQL Control Flow Functions

    Posted on May 21st, 2009 Sandeep 1 comment

    Sure, the documentation for control flow functions is available right here. But it’s not the easiest thing in the world to understand. The documentation, I mean – since the examples given there are very simple cases.

    Here’s a portion of it, explaining the CASE statement:

    CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

    CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

    The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSEĀ  part.

    mysql> SELECT CASE 1 WHEN 1 THEN 'one'
        ->     WHEN 2 THEN 'two' ELSE 'more' END;
            -> 'one'
    mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
            -> 'true'
    mysql> SELECT CASE BINARY 'B'
        ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
            -> NULL

    Great, but how do you use it in an actual scenario where you want to, let’s say, update a column based on its current contents?

    Read the rest of this entry »