How Can I Use MySQL\'s CASE Statement to Assign Values Based on Conditions?

MySQL CASE Syntax Explained

SQL's CASE statement allows you to conditionally execute different sets of statements based on specific conditions. It's not an if-else statement; rather, it's more analogous to a switch statement.

To understand how CASE works, consider the following example:

Problem:

You have a field named user_role in the user table, which contains user roles such as "Manager", "Part Time", etc. You need to generate a new field named role_order that assigns a different number to each role. For instance, if user_role = 'Manager', then role_order should be 5.

Solution:

Using the CASE syntax, you can implement this logic as follows:

<code class="sql">CASE 
    WHEN user_role = 'Manager' then 5
    WHEN user_role = 'Part Time' then 7
    ELSE -1 --unknown
END</code>

Here, the CASE statement:

• Examines the value of the user_role field
• If it equals 'Manager', it returns 5
• If it equals 'Part Time', it returns 7
• If it doesn't match any of the specified values, it returns -1 (representing an unknown role)

Another Syntax Option:

If you're only examining a single value, you can use a more concise syntax:

<code class="sql">CASE user_role
    WHEN 'Manager' then 5
    WHEN 'Part Time' then 7
    ELSE -1 --unknown
END</code>

Remember, CASE allows you to evaluate multiple conditions and conditionally execute different statements. It's a powerful tool for managing conditional logic in SQL queries.

The above is the detailed content of How Can I Use MySQL\'s CASE Statement to Assign Values Based on Conditions?. For more information, please follow other related articles on the PHP Chinese website!