JIYIK CN >

In this article, we will learn to update multiple columns with different values ​​in multiple rows in MySQL using CASE statement, IF() function, INSERT ... ON DUPLICATE KEY UPDATEclause and function.UPDATE with JOIN()

Update multiple columns in multiple records (rows) with different values ​​in MySQL

Sometimes, we need to update multiple columns in multiple rows with different values ​​in the database. If there are several records in the table, multiple UPDATE statements can be used.

Assume there are millions of rows in the table. Listed below are some ways to update the table.

1. Use the CASE statement.
2. Use the IF() function.
3. Use INSERT ... ON DUPLICATE KEY UPDATE.
4. Use UPDATE with JOIN().

To learn the above approach, create a table called students with ID, JavaScore, and PythonScore as attributes (columns), where ID is the primary key. You can follow this tutorial using the following queries to create and populate the table.

Sample code:

# create a table
CREATE TABLE students(
  ID INT NOT NULL,
  JavaScore INT NOT NULL,
  PythonScore INT NOT NULL,
  PRIMARY KEY (ID));

# insert data
INSERT INTO students (ID, JavaScore, PythonScore)
VALUES
(1, 70, 65),
(2, 75, 80),
(3, 81, 89),
(4, 50, 70);

# display table data
SELECT * FROM students;

Output:

ID    JavaScore    PythonScore
1    70    65
2    75    80
3    81    89
4    50    70

Once the students table is created and populated, we can use the above method.

Using the CASE Statement

Sample code:

UPDATE students
    SET JavaScore = (case
                    when ID = 1 then 75
                    when ID = 2 then 80
                    when ID = 3 then 86
                    when ID = 4 then 55
                    end),
        PythonScore = (case
                    when ID = 1 then 70
                    when ID = 2 then 85
                    when ID = 3 then 94
                    when ID = 4 then 75
                    end)
    WHERE ID in (1,2,3,4);

Use the SELECT statement to obtain the updated results.

SELECT * FROM students;

Output:

ID    JavaScore    PythonScore
1    75    70
2    80    85
3    86    94
4    55    75

We update multiple columns of multiple rows with different values ​​using a CASE statement, which iterates through all conditions and outputs an item (value) when the first condition is met (like an if-then-else statement). Once a condition is true, it stops reading and returns the corresponding result.

If there is no TRUE condition, the ELSE part is executed. In the absence of ELSE part, it returns NULL.

If there is another field of type DATETIME that we want to remain constant for all records, the query would look like this.

Sample code:

UPDATE students
    SET JavaScore = (case
                    when ID = 1 then 75
                    when ID = 2 then 80
                    when ID = 3 then 86
                    when ID = 4 then 55
                    end),
        PythonScore = (case
                    when ID = 1 then 70
                    when ID = 2 then 85
                    when ID = 3 then 94
                    when ID = 4 then 75
                    end),
        DATEANDTIME = NOW()

    WHERE ID in (1,2,3,4);

Using the IF() Function

Sample code:

UPDATE students SET
    JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))),
    PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL))))
WHERE ID IN (1,2,3,4);

Execute the SELECT command to get the new values ​​of the student table.

SELECT * FROM students;

Output:

ID    JavaScore    PythonScore
1    76    71
2    81    86
3    87    95
4    56    76

We use the function to return a specific value if the condition is met IF(). Otherwise, it returns another specified value. Its syntax is IF(condition, TrueValue, FalseValue).

You may have a question, if the condition of ID=1 is met , then why do we need another IF()? We use the following nested IF() functions to create multiple IFs.

IF(condition, TrueValue,
  IF(condition, TrueValue,
    IF(condition, TrueValue,
      IF(condition, TrueValue, FalseValue)
      )
    )
  )

Let's make it easier to understand. In the following code snippet, we have multiple IFs and it doesn't matter if the condition is met or not.

Each IF condition is checked and the value is set accordingly. The last IF has an ELSE part that will only run if the fourth IF condition is FALSE.

IF Condition
    TrueValue
IF Condition
    TrueValue
IF Condition
    TrueValue
IF Condition
    TrueValue
ELSE
    FalseValue

The reason for using nested IF()functions is to update multiple rows with different values.

When you need to update multiple columns in multiple rows, we prefer to use the CASE statement because it IF()is easier to understand and manage than nested functions.

Using INSERT on Duplicate Key Updates...

Sample code:

INSERT INTO students (ID, JavaScore, PythonScore)
VALUES
(1, 77, 72),(2, 82, 87),(3, 88, 96),(4, 57, 77)
ON DUPLICATE KEY UPDATE
JavaScore = VALUES(JavaScore),
PythonScore = VALUES(PythonScore);

Output:

ID    JavaScore    PythonScore
1    77    72
2    82    87
3    88    96
4    57    77

This example shows that INSERT ... ON DUPLICATE KEY UPDATE, normally, when we INSERT into a particular table, it may cause duplicates in the PRIMARY KEY or UNIQUE index, which will cause an error.

However, if we specify ON DUPLICATE KEY UPDATE, MySQL updates the existing record with the latest value. If a duplicate is found in the PRIMARY KEY, then the value of that particular column will be set to its current value.

Although the function works fine at the time of writing this tutorial VALUES(), it displays a warning stating that the VALUES() function is deprecated and will be removed in a future version. You can consider the MySQL documentation for further assistance.

Using UPDATE with JOIN()

Sample code:

UPDATE students std
JOIN (
    SELECT 1 AS ID, 78 AS JavaScore, 73 AS PythonScore
    UNION ALL
    SELECT 2 AS ID, 83 AS JavaScore, 88 AS PythonScore
    UNION ALL
    SELECT 3 AS ID, 89 AS JavaScore, 97 AS PythonScore
    UNION ALL
    SELECT 4 AS ID, 58 AS JavaScore, 78 AS PythonScore
) temp
ON std.ID = temp.ID
SET std.JavaScore = temp.JavaScore, std.PythonScore = temp.PythonScore;

This solution will only work if safe mode is disabled. We can disable it in MySQL Workbench by going to Edit->Preferences->SQL Editor and unchecking the Safe Mode option.

Then, restart MySQL server, execute the query given above and SELECT * FROM students;get the following results using command.

Output:

ID    JavaScore    PythonScore
1    78    73
2    83    88
3    89    97
4    58    78

We collect the data in using SELECTand . Once completed, we concatenate all the data using and set the JavaScore and PythonScore for each condition that satisfies the ID property.UNION ALLJOIN()JOIN()