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 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:
UPDATE mytable
SET
foo= IF(foo IS NULL, '1', foo),
bar = '2',
baz = IF(baz > '10', 'high', 'low')
WHERE id='69'
You can do all kinds of behind-the-scenes magic with a query like that, and make it as simple or as complex as you want.
Anyways, if this post bored you to tears, I apologize. Stuff like this will crop up here every once in a while though. It’s just a fact of life.
For what it’s worth, I like these posts. Keep ’em coming.
Or, why not just the more portable:
UPDATE mytable SET foo=’bar’ WHERE id=’69’ and foo IS NULL;
@ Tino:
LOL! Good point. I’m making this more complex than it needs to be, aren’t I?
Then again, the traditional format won’t work in the second high-low example so this format is still useful for creating compact MySQL queries. :)
Luke Maciak wrote:
:D. But, as you say, it is a good hint that the IF operator (or CASE, which I thought were more portable?) can be useful also as part of UPDATEs, and not only SELECTs.