MySQL:仅在满足条件时更新字段
是否可以在 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.
相关文章