I have this table:
CREATE TABLE IF NOT EXISTS `my_table` (
`A` int(11) NOT NULL,
`B` int(11) NOT NULL,
`C` varchar(50) NOT NULL,
`D` varchar(30) NOT NULL,
PRIMARY KEY (`A`,`B`,`C`)
)
I want to update several entries in just one query. I tried this:
UPDATE my_table
SET D = CASE
WHEN (A = 6 AND B = 1 AND C = 'red') THEN '1#2#3#5#4'
WHEN (A = 8 AND B = 1 AND C = 'green') THEN '5#6#7#8#9'
END
But this query updates all entries in the table. It updates perfectly the value of 'D' of the two entries I want to update, but it also deletes the values of "D" of the other entries, and I want them to stay with their previous values.
The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.
In this article, we will see, how to update multiple columns in a single statement in SQL. We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.
The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.
If you do not explicitly add an else
clause to a case
expression, it implicitly acts as though you've added else null
to it. So, your update statement is effectively equivalent to:
UPDATE my_table
SET D = CASE
WHEN (A = 6 AND B = 1 AND C = 'red') THEN '1#2#3#5#4'
WHEN (A = 8 AND B = 1 AND C = 'green') THEN '5#6#7#8#9'
ELSE NULL
END
Which explains why you see D
being "deleted".
One way around it is to explicitly add an else
clause that simply returns D
:
UPDATE my_table
SET D = CASE
WHEN (A = 6 AND B = 1 AND C = 'red') THEN '1#2#3#5#4'
WHEN (A = 8 AND B = 1 AND C = 'green') THEN '5#6#7#8#9'
ELSE D
END
Another way, which is a bit "clunkier" in syntax, but may perform slightly better, is to add a where
clause so only the relevant rows are updated:
UPDATE my_table
SET D = CASE
WHEN (A = 6 AND B = 1 AND C = 'red') THEN '1#2#3#5#4'
WHEN (A = 8 AND B = 1 AND C = 'green') THEN '5#6#7#8#9'
END
WHERE (A = 6 AND B = 1 AND C = 'red') OR (A = 8 AND B = 1 AND C = 'green')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With