MySQL: Conditional SQL Statement to handle Setting Null Values if Not Set CodeUnit 07 NOV 2011

Identifying column values in your database that are currently set to NULL and then updating them to a default value is made easy through the use of the common UPDATE and not so common IFNULL function in MySQL.

If say for example you have a legacy `color` column that used to allow NULL entries in your database, but which you now want to force to have a color, with a starting default of say ‘blue’, you can quite easily achieve this through a simple UPDATE statement which will indiscriminately set all the records having a NULL value for the `color` column such that they now reflect ‘blue’ as their value.

This query would look like:

UPDATE `pencil-box` SET `color` = IFNULL(`color`,'blue')

The IFNULL function accepts two parameters. If the first parameter is not null, then it returns the first parameter. If it is null, then the function returns the second parameter. So in our example above, any `color` value that already exists is simply replaced with itself again. However, if one comes up as being NULL, it gets replaced with ‘blue’.


Related Link:

Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.