im trying to update multiple columns in a table that all needs different where clauses in single query. Im not amazing at sql, and struggle to find help on google. im trying to achieve something like this
UPDATE $table
SET meta_value = 'john',
meta_value = 'v1234'
WHERE meta_key = 'name' AND post_id=$post_id,
meta_key = 'trak' AND post_id=$post_id"
the idea being that the first SET matches the first WHERE, and the 2nd SET matches the 2nd WHERE etc.. ideally id be able to do many of these. any idea how I can achieve this?
asked Mar 13, 2015 at 19:39
2
Update $table
set meta_value = CASE WHEN meta_key = 'name' then 'john'
WHEN meta_key = 'trak' then 'v1234' end
where post_Id = $post_ID
answered Mar 13, 2015 at 19:46
xQbertxQbert
33.9k2 gold badges39 silver badges60 bronze badges
5
UPDATE $table
SET meta_value = IF[meta_key='name','john','v1234']
WHERE post_id=$post_id AND
meta_key IN ['trak','name']
answered Mar 13, 2015 at 19:45
symcbeansymcbean
46.9k6 gold badges56 silver badges90 bronze badges
4
Created: May-06, 2022 In this article, we’ll learn the use of the Sometimes, we
need to update multiple columns in multiple rows with different values in the database. It is ok to use multiple Suppose there are millions of rows in the table. Some of the ways to update the table are listed below. To learn the approaches mentioned above, create a table named Example Code: Output: Once the Example Code: Use the Output: We update multiple columns on multiple rows with different values using the Suppose there are no If there is another field of the Example Code: Example Code: Execute 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
UPDATE
statements if we have a few records in the table.CASE
statement.IF[]
function.INSERT ... ON DUPLICATE KEY UPDATE
.UPDATE
with JOIN[]
.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.# 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;
IDJavaScorePythonScore 1
70
65
2
75
80
3
81
89
4
50
70
students
table is created and populated, we can use the mentioned approaches.Use the
CASE
StatementUPDATE 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];
SELECT
statement to get the updated results.SELECT * FROM students;
IDJavaScorePythonScore 1
75
70
2
80
85
3
86
94
4
55
75
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.TRUE
conditions, then the ELSE
part is executed. In the absence of the ELSE
section, it returns
NULL
.DATETIME
type that we want to keep constant for all the records, the query would be as follows.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[]
FunctionUPDATE 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];
SELECT
command to get the new values of the students
table.
SELECT * FROM students;
Output:
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:
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:
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.