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:
1 2 3 4 | update marks set highest_mark = 10 -- insert new value here where student_id = 42 -- insert id and highest_mark < 10 -- condition |
Nothing extraordinary! Simply use comparison operations to use within where condition.
And to smartly assign greatest value in database column, use as follow:
1 2 | update marks set highest_mark = GREATEST(highest_mark, 15) where student_id = 42 -- insert id |
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
1 2 3 4 | INSERT INTO marks (student_id, highest_mark) VALUES (42, 15) ON DUPLICATE KEY UPDATE highest_mark = GREATEST(highest_mark, VALUES(highest_mark)) |
Really very happy to say,your post is very interesting to read.I never stop myself to say something about it.You’re doing a great job.Keep it up
smart way to save extra queries. thank you