MySQL: 只有满足条件时才更新字段

是否有可能在 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

做这件事的正确方法是什么？

182774 次浏览

小开

Try this:

UPDATE test
SET
field = 1
WHERE id = 123 and condition

小开

最佳答案

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.

小开

Another solution which, in my opinion, is easier to read would be:

UPDATE test
SET something = 1, field = IF(condition is true, 1, field)
WHERE id = 123

What this does is set 'field' to 1 (like OP used as example) if the condition is met and use the current value of 'field' if not met. Using the previous value is the same as not changing, so there you go.

小开

Another variation:

UPDATE test
SET field = IF ( {condition}, {new value}, field )
WHERE id = 123

This will update the field with {new value} only if {condition} is met

小开

Another solution we can use MySQL IF() conditional function :

UPDATE test
SET  field  = IF(something == 1{CONDITION}, 1 {NEW VALUE}, field)
WHERE `id` = 5

小开

found the solution with AND condition:

  $trainstrength = "UPDATE user_character SET strength_trains = strength_trains + 1, trained_strength = trained_strength +1, character_gold = character_gold - $gold_to_next_strength WHERE ID = $currentUser AND character_gold > $gold_to_next_strength";