0 0 Votes

MySQL: Update only if certain value in field before

Question by Guest | 08/09/2012 at 15:28

I have a little problem with a database query in MySQL. I want to change a value in one field with an UPDATE command. However, a change should only be carried, if there is a specific value stored in the field before. Otherwise, the old value should be kept.

Example: I want to set the field "foo" in the table "users" to 3 where the id = 10, but only when the field was previously set to 2. Otherwise, the field should keep the previous value, because the values should only become bigger and not smaller.


Stefan Trost

Show Profile | Message
1Best Answer
1 Vote

You can use if-statements in MySQL, in order to manage a case. To take your example:

UPDATE users SET foo=IF(foo=2,3,foo) WHERE id=10

Behind the 'foo =', there is such an IF statement. If 'foo = 2', the first option after the first comma is used (the 3 is written in the field). If 'foo = 2', the second alternative behind the second comma is taken. That is foo, and thus the old value of the field remains. It is important to mention this extra, otherwise afterwards there will be no value in the field.

You also wrote, "the values should only become bigger and not smaller" Perhaps, therefore, also this statement could be interesting for you:

UPDATE users SET foo=IF(foo<3,3,foo) WHERE id=10

With this, foo is set to 3, if foo was smaller than 3 previously. So, foo also will be set to 3, if there was a 0 or a 1 in the field before, not only if there was a 2.
10/09/2012 at 20:48

ReplyPositive Negative

Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.


Ask your own question or write your own articles on askingbox.com. How to do.