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?
This question is related to
mysql
if-statement
sql-update
Another solution we can use MySQL IF()
conditional function :
UPDATE test
SET field = IF(something == 1{CONDITION}, 1 {NEW VALUE}, field)
WHERE `id` = 5
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
Try this:
UPDATE test
SET
field = 1
WHERE id = 123 and condition
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";
Source: Stackoverflow.com