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

Updated on 03-Oct-2019 07:49:27

• Related Questions & Answers
• MySQL update column to NULL for blank values
• How to skip blank and null values in MySQL?
• Java Program to deselect all cells in a JTable
• Java Program to select all cells in a table
• Sum if all rows are not null else return null in MySQL?
• Treat a MySQL column field as NULL if it is blank?
• How to merge table cells in HTML?
• How to check for null, undefined, or blank variables in JavaScript?
• What MySQL ASCII() function returns if I will provide NULL to it?
• What will MySQL CHAR_LENGTH() function return if I provide NULL to it?
• Convert MySQL null to 0?
• Typecasting NULL to 0 in MySQL
• Can MySQL INT type be non-zero NULL?
• Get the sum only from specific cells in MySQL?
• C++ program to find sum of all cells lighten by the lamps