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

Update multiple rows with different values ​​using a single MySQL query

The operation of updating multiple rows with different values ​​in each row in MySQL seems complicated, but it is actually very simple after understanding. Here’s how to do it:

The following example code attempts to update three rows in the 'table_users' table based on specific conditions, with different values ​​for the 'cod_user' and 'date' fields. For this we will use 'UPDATE' statement with 'CASE' and 'IN' statements.

The following query performs these updates:

<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 = '2014-08-12'
WHERE user_role IN ('student', 'assistant', 'admin')
  AND cod_office = '17389551';</code>

Query breakdown:

• The 'UPDATE' statement first specifies the name of the table to be updated, in this case 'table_users'.
• The 'SET' clause defines the columns to be updated and the new values ​​they should take. In this query, we are using 'CASE' statement to assign different values ​​to 'cod_user' based on the value of 'user_role'.
• The 'WHERE' clause specifies conditions that determine which rows are updated. It uses the 'IN' statement to check if 'user_role' is one of ('student', 'assistant', 'admin') and if 'cod_office' matches '17389551'.

Please note that the date format used in the examples is not recommended. Best practice is to store dates as native date types (such as YYYY-MM-DD).

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