MySQL: Conditional Update

You learn something new every day. Here is a neat little SQL trick that I just learned. As usual, this post is mostly here for my future reference, and of course the overall good of the humankind. I hope someone will find it helpful. If not, however I apologize for being boring. You know, I think I got into blogging for two reasons. One of these reasons was to have an outlet where I could complain about “the fucking lusers” that I was dealing with on a daily basis. The other reason was to provide a reliable and searchable long term repository for knowledge tidbits I was picking up here and there. You know, like that awesome command line trick that I use once every two years, or some SQL acrobatics that I spent 4 hours assembling together to fit a very specific problem. I found that no matter how obscure a problem is, chances are that it will come up again at some point so it pays to keep notes.

The problem for today goes like this: there is a table in the database, and it has a field foo that holds some information. The field is initially NULL, and there is an update query that changes it to ‘bar’ when the user does something. But we want to update foo only when it’s NULL and leave it alone otherwise. In other words, if it already contains the value ‘baz’ we want to keep it the way it is.

The challenge? Do it without modifying any existing logic, and without introducing a second query to look up the value of foo. How to we accomplish this by simply modifying the existing update query? It’s easy – just use the built in MySQL branching IF statement like this:

UPDATE mytable
SET foo=IF(foo ISNULL,'bar', foo)WHERE id='69'

UPDATE mytable
SET foo= IF(foo IS NULL, 'bar', foo)
WHERE id='69'

This will test whether or not foo is NULL, and then set it to ‘bar’ if it is, or re-set it to whatever it is right now otherwise. Now this may seem wasteful (ie. the update on false condition) but imagine this update statement actually sets not just foo but a number of different fields like this:

Your email address will not be published. Required fields are marked *

Comment

Name *

Email *

Website

Currently you have JavaScript disabled. In order to post comments, please make sure JavaScript and Cookies are enabled, and reload the page.Click here for instructions on how to enable JavaScript in your browser.