MySQL: update a field only if condition is met
MysqlIf StatementSql UpdateMysql Problem Overview
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
In other words:
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?
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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.
Solution 3 - Mysql
Try this:
UPDATE test
SET
field = 1
WHERE id = 123 and condition
Solution 4 - Mysql
Another solution we can use MySQL IF()
conditional function :
UPDATE test
SET field = IF(something == 1{CONDITION}, 1 {NEW VALUE}, field)
WHERE `id` = 5
Solution 5 - Mysql
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
Solution 6 - Mysql
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";