How to Update Multiple Columns in Multiple Rows With Different Values 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.
- Use the
CASE
statement. - Use the
IF()
function. - Use
INSERT ... ON DUPLICATE KEY UPDATE
. - Use
UPDATE
withJOIN()
.
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:
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 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:
ID | JavaScore | PythonScore |
---|---|---|
1 | 75 | 70 |
2 | 80 | 85 |
3 | 86 | 94 |
4 | 55 | 75 |
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:
ID | JavaScore | PythonScore |
---|---|---|
1 | 76 | 71 |
2 | 81 | 86 |
3 | 87 | 95 |
4 | 56 | 76 |
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:
ID | JavaScore | PythonScore |
---|---|---|
1 | 77 | 72 |
2 | 82 | 87 |
3 | 88 | 96 |
4 | 57 | 77 |
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:
ID | JavaScore | PythonScore |
---|---|---|
1 | 78 | 73 |
2 | 83 | 88 |
3 | 89 | 97 |
4 | 58 | 78 |
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.