In the documentation, it doesn't mention which version
of MySQL supports the CASE statement, but it refers to
stored procedures, so is it only supported for 5.x?
I can't get any example of a CASE statement work in
The reason I'm asking is because in other SQL
dialects, such as T-SQL, you don't have to use stored
procedures to use a CASE statement. You can use it in
the SELECT field list or even to assign to fields in
the UPDATE statement.
For example, I can execute the following in MS SQL
field1 = CASE WHEN field3 = 1 THEN 10 ELSE 20 END,
field2 = field2 | CASE WHEN field4 = 'B' THEN 1 ELSE 0
END | CASE WHEN field4 = 'C' THEN 2 ELSE 0 END
In the above example, I'm using the result of the CASE
statement as the R-value in the assignment of field1
and as the operands to a bitwise OR expression
assigned to field2.
It's logically equivalent to C/C++/Java/C# tertiary
operator (?:) and VB's IIF. And it's commonly used to
reduce fields to a bit mask.
Is there a construct that accomplishes the same thing
in MySQL 4.1.x?