MySQL update a field only if condition is met

Question

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

User · Answer

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  gt   gold to next strength

User · Answer

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

User · Answer

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

User · Answer

Another solution we can use MySQL IF   conditional function    UPDATE test SET  field    IF something    1 CONDITION   1  NEW VALUE   field  WHERE  id    5

User · Answer

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

User · Answer

Try this   UPDATE test SET    field   1 WHERE id   123 and condition

[mysql] MySQL: update a field only if condition is met

Examples related to mysql

Examples related to if-statement

Examples related to sql-update