mysql 符合条件更新_MySQL：仅在满足条件时更新字段

Is it possible to do an UPDATE query in MySQL which updates field value only if certain condition is met? Something like this:

UPDATE test

SET

CASE

WHEN true

THEN field = 1

END

WHERE id = 123

In other words:

UPDATE test

SET

something = 1, /*field that always gets updated*/

CASE

WHEN true

THEN field = 1 /*field that should only get updated when condition is met*/

END

WHERE id = 123

What is the proper way to do this?

解决方案

Yes!

Here you have another example:

UPDATE prices

SET final_price= CASE

WHEN currency=1 THEN 0.81*final_price

ELSE final_price

END

This works because MySQL doesn't update the row, if there is no change, as mentioned in docs:

If you set a column to the value it currently has, MySQL notices this

and does not update it.