MySQL update if value is greater than current value

While working with MySQL database there might be situation when you wish to update certain column only if the value being updated is greater than existing value or current value stored in that column. MySQL GREATEST and LEAST functions are quite handy in such situations. Let’s see how can we use first function to update a column if value is greater than current value.

MySQL update if value is greater than current value

Suppose we have a marks table with column student_id and highest_mark and we wish to update highest_mark only if it’s greater than a certain value or stored value. If you wish to update if it’s greater than a certain value then idea is quite easy:

Nothing extraordinary! Simply use comparison operations to use within where condition.

And to smartly assign greatest value in database column, use as follow:

Change the number 15 with the value you have. The GREATEST function will automatically assign greater value between current value in database and value provided by you.

And also use as INSERT INTO ON DUPLICATE KEY UPDATE

 

You Might Interested In