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

2021-11-20 00:00:00 sql-update if-statement 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.

相关文章