How to Efficiently Update Multiple MySQL Rows with Different Values?

Efficiently update different values ​​of MySQL multi-row records

Introduction

Updating multiple rows with different values ​​can be challenging, especially with complex WHERE conditions. This article aims to simplify this process by explaining in detail the two most commonly used methods: multi-statement queries and conditional updates.

Method 1: Multi-statement query

Multi-statement queries allow you to execute multiple UPDATE commands in one query. While this method is simple and straightforward, it can become unwieldy for large data sets. The example you provided can be implemented as follows:

<code class="language-sql">UPDATE table_users
SET cod_user = '622057', date = '12082014'
WHERE user_rol = 'student' AND cod_office = '17389551';

UPDATE table_users
SET cod_user = '2913659', date = '12082014'
WHERE user_rol = 'assistant' AND cod_office = '17389551';

UPDATE table_users
SET cod_user = '6160230', date = '12082014'
WHERE user_rol = 'admin' AND cod_office = '17389551';</code>

Method 2: Conditional update using CASE statement

Conditional updates allow you to apply different values ​​to different rows based on conditions. The example you provided could be rephrased as follows:

<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>

This method is more efficient than multi-statement queries, especially in the case of large data sets. It also provides a cleaner and clearer way to update multiple rows of records.

Note:

Be sure to store dates in a consistent, database-native format to avoid potential problems.

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