How Can I Update Multiple Rows with Different Values in a Single MySQL Query?

Efficiently Updating Multiple MySQL Rows with Varying Values

MySQL offers several methods for updating multiple rows with distinct values in a single query. While IF statements can work, the CASE statement provides a more concise and readable solution, especially when dealing with multiple conditions.

Let's consider a scenario where we need to update the table_users table, assigning different cod_user values based on user roles within a specific office. A cleaner approach than using multiple IF statements is demonstrated below:

This query utilizes the CASE statement to update cod_user based on user_rol:

<code class="language-sql">UPDATE table_users
SET cod_user = CASE
    WHEN user_rol = 'student' THEN '622057'
    WHEN user_rol = 'assistant' THEN '2913659'
    WHEN user_rol = 'admin' THEN '6160230'
    END,
date = '12082014'
WHERE user_rol IN ('student', 'assistant', 'admin')
AND cod_office = '17389551';</code>

Here's a breakdown:

• CASE statement: This elegantly handles multiple conditions. Each WHEN clause checks a condition, and if true, assigns the corresponding cod_user value.
• Multiple conditions: The CASE statement efficiently manages the different user roles.
• date update: The date field is updated to '12082014' for all matching rows. Remember to adjust the date format ('YYYYMMDD' in this example) to match your database settings.
• WHERE clause: This filters the update to only affect rows where user_rol is 'student', 'assistant', or 'admin' and cod_office is '17389551'.

This method provides a more efficient and maintainable solution compared to using multiple individual UPDATE statements or complex nested IF structures. The CASE statement enhances readability and simplifies the overall query logic.

The above is the detailed content of How Can I Update Multiple Rows with Different Values in a Single MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!