MySQL：仅在满足条件时更新字段 [英] MySQL: update a field only if condition is met

问题描述

是否可以在MySQL中执行UPDATE查询，只有在满足某些条件时才更新字段值？这样的事情：

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

换句话说：

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?

推荐答案

是的！

这里有另一个例子：

UPDATE prices
SET final_price= CASE
   WHEN currency=1 THEN 0.81*final_price
   ELSE final_price
END

这是因为MySQL没有更新行，如果没有变化，如文档中所述：

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

如果将列设置为当前值，则MySQL会注意到此
并且不会更新它。

If you set a column to the value it currently has, MySQL notices this and does not update it.

这篇关于MySQL：仅在满足条件时更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！