MYSQL: Most sufficient way to to update only fields that are not empty?

Question

Let's say I have this table:

ID | col1 | col2 | col3 | col4
1  |  val |      |  val |

Is there a way to modify this query:

UPDATE table set col1 = "bla", col2 = "bla", col3 = "bla", col4 = "bla where id = 1

So that I end up with:

ID | col1 | col2 | col3 | col4
1  |  val |  bla |  val |  bla

In other words, the query must only update the fields that are not null. How do you do that?

Answer 1

the simpliest answer is to use COALESCE

UPDATE table 
set     col1 = COALESCE(col1,"bla"), 
        col2 = COALESCE(col2,"bla"), 
        col3 = COALESCE(col3,"bla"), 
        col4 = COALESCE(col4,"bla")
where   id = 1

• SQLFiddle Demo

Other links.

• MySQL COALESCE