Updating blank cells to NULL will cause all cells to be NULL in MySQL?

To update only blank cells to NULL, use NULLIF() in MySQL. Let us first create a table −

mysql> create table DemoTable
(
   Name varchar(50)
);
Query OK, 0 rows affected (1.73 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('Mike');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values('');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values('David');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values('');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('Mike');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-------+
| Name  |
+-------+
| Mike  |
|       |
| David |
|       |
| Mike  |
+-------+
5 rows in set (0.00 sec)

Following is the query to update only blank cells to NULL −

mysql> update DemoTable
   set Name=NULLIF(Name,'');
Query OK, 2 rows affected (0.19 sec)
Rows matched: 5 Changed: 2 Warnings: 0

Let us check the table records once again −

mysql> select *from DemoTable;

This will produce the following output −

+-------+
| Name  |
+-------+
| Mike  |
| NULL  |
| David |
| NULL  |
| Mike  |
+-------+
5 rows in set (0.00 sec)

AmitDiwan

Published on 03-Oct-2019 11:18:33