Update multiple columns in MySQL but only when a value is not null

Question

I have a MySQL table with multiple columns, A, B, and C.

I would like to update those columns using only one SQL statement. However, sometimes some of the columns can be null.

So, if A is null, only update B and C.

If A and B are null, only update C.

And so on, with all other combinations.

How can I do that in one single statement?

Thanks.

Answer 1

You can use if within your update clause:

update test_update set A=if(A is null, null, 'A2'), B=if(B is null, null, 'B2'), C=if(C is null, null, 'C2');

Example run:

MariaDB [test]> select * from test_update;
+------+------+------+
| A    | B    | C    |
+------+------+------+
| A1   | NULL | NULL |
| NULL | B1   | NULL |
| NULL | NULL | C1   |
| A1   | B1   | NULL |
| A1   | NULL | C1   |
| NULL | B1   | C1   |
| A1   | B1   | C1   |
+------+------+------+
7 rows in set (0.00 sec)

MariaDB [test]> update test_update set A=if(A is null, null, 'A2'), B=if(B is null, null, 'B2'), C=if(C is null, null, 'C2');
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

MariaDB [test]> select * from test_update;
+------+------+------+
| A    | B    | C    |
+------+------+------+
| A2   | NULL | NULL |
| NULL | B2   | NULL |
| NULL | NULL | C2   |
| A2   | B2   | NULL |
| A2   | NULL | C2   |
| NULL | B2   | C2   |
| A2   | B2   | C2   |
+------+------+------+
7 rows in set (0.00 sec)

Answer 2

It seems strange that you want to update only non-NULL values, but that is how you've written the statement. I would write it as:

update test_update
    set A = (case when A is not null then 'A' end),
        B = (case when B is not null then 'B' end),
        C = (case when C is not null then 'C' end)
     where A is not null or B is not null or C is not null;

The constant strings are, of course, whatever new values you want.