Update multiple columns with different conditions in mysql

In this article, we’ll learn the use of the CASE statement, IF() function, INSERT ... ON DUPLICATE KEY UPDATE clause and UPDATE with JOIN() function to update multiple columns in multiple rows with different values in MySQL.

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. It is ok to use multiple UPDATE statements if we have a few records in the table.

Suppose there are millions of rows in the table. Some of the ways to update the table are listed below.

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 approaches mentioned above, create a table named students having ID, JavaScore, and PythonScore as attributes (columns) where ID is a primary key. You can follow this tutorial by using the queries below to create and populate the table.

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

Once the students table is created and populated, we can use the mentioned approaches.

Use the CASE Statement

Example 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 get the updated results.

SELECT * FROM students;

Output:

We update multiple columns on multiple rows with different values using the CASE statement that goes through all conditions and outputs an item (value) when the first condition is satisfied (like the if-then-else statement). It stops reading once the condition is TRUE and returns the corresponding result.

Suppose there are no TRUE conditions, then the ELSE part is executed. In the absence of the ELSE section, it returns NULL.

If there is another field of the DATETIME type that we want to keep constant for all the records, the query would be as follows.

Example 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);

Use the IF() Function

Example 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 students table.

SELECT * FROM students;

Output:

We use the IF() function that returns a particular value if the condition is satisfied. Otherwise, it returns another specified value. It’s syntax is IF(condition, TrueValue, FalseValue).

You may have a question if the ID=1 condition meets, then why is it going to another IF()? We use nested IF() functions as follows to make multiple IFs.

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

Let’s make it more simple to understand. In the following snippet, we have multiple IFs, and it doesn’t matter whether the condition is met or not.

Every IF condition would be checked and set the value accordingly. The last IF has the ELSE part, which will only run if the fourth IF condition is FALSE.

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

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

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

Use INSERT ... ON DUPLICATE KEY UPDATE

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

This example shows INSERT ... ON DUPLICATE KEY UPDATE. Normally, when we INSERT into a particular table where it may cause a duplicate in the PRIMARY KEY or UNIQUE index, it causes an error.

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

Although the VALUES() function is working when writing this tutorial, it shows a warning that the VALUES() function is deprecated and will be removed in a future release. You may consider MySQL Documentation for further assistance.

Use UPDATE With JOIN()

Example 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 the safe mode is disabled. We can disable it in MySQL Workbench by going to Edit->Preference->SQL Editor and unchecking the Safe Mode option.

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

Output:

We gather the data inside the JOIN() using SELECT and UNION ALL. Once it is done, we join all the data using JOIN() and set the JavaScore and PythonScore on every satisfying condition for the ID attribute.

Related Article - MySQL Column