MySQL:仅在满足条件时更新字段

Question

是否可以在 MySQL 中执行 UPDATE 查询，仅在满足特定条件时才更新字段值？像这样的:

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

这样做的正确方法是什么？

Answer 1

是的!

这里有另一个例子:

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

这是因为 MySQL 不会更新行，如果没有变化，as mentioned in docs :

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