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

Efficiently Updating Multiple MySQL Rows with Different Values

Database management often requires updating multiple rows with unique values. MySQL offers an efficient solution using the UPDATE statement in conjunction with CASE expressions.

Addressing Conditional Updates

The challenge lies in updating rows based on various conditions. Imagine a table with user roles, IDs, and office codes; you need to update user IDs according to their role and office.

Leveraging CASE Expressions

MySQL's CASE expressions enable assigning different values based on conditions. This perfectly addresses the need for conditional updates.

Implementing the Solution

The following syntax updates multiple rows with distinct values based on multiple criteria:

<code class="language-sql">UPDATE table_name
SET column_name = (
    CASE
        WHEN condition1 THEN value1
        WHEN condition2 THEN value2
        ...
    END
),
column_name2 = value2
WHERE condition;</code>

Illustrative Example:

Let's update cod_user and date columns for different user roles within a specific office:

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

Important Considerations:

• Maintain data type consistency (dates, numbers, etc.).
• Expand CASE expressions to handle more conditions as needed.
• This approach streamlines updates, enhancing efficiency and data integrity.

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