MySQL query to replace only the NULL values from the table?

For this, you can use the property IS NULL for null values in MySQL. Let us first create a table −

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

Insert some records in the table using insert command −

mysql> insert into DemoTable values('Robert');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values(null);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('David');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values(null);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('Robert');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

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

Following is the query to replace null values from the table −

mysql> update DemoTable set Name=IF(Name IS NULL,'Please Enter a Name',Name);
Query OK, 2 rows affected (0.09 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                |
+---------------------+
| Robert              |
| Please Enter a Name |
| David               |
| Please Enter a Name |
| Robert              |
+---------------------+
5 rows in set (0.00 sec)

AmitDiwan

Published on 30-Sep-2019 08:21:32

• Related Questions & Answers
• MySQL query to replace only the backslashes from folder path?
• MySQL query to display only the empty and NULL values together?
• MySQL query to compare and display only the rows with NULL values
• MySQL query to exclude some of the values from the table
• Return only the non-empty and non-null values from a table and fill the empty and NULL values with the corresponding column values in MySQL?
• Set value only for NULL values in a MySQL table
• Remove only the percentage sign from values in a MySQL table?
• MySQL query to skip the duplicate and select only one from the duplicated values
• Replace the empty values from a MySQL table with a specific value
• MySQL query to get only the minutes from datetime?
• MySQL query to order by NULL values
• MySQL replace values in a table?
• How to remove only the first word from columns values with a MySQL query?
• MySQL query to return the count of only NO values from corresponding column value
• MySQL query to count all the column values from two columns and exclude NULL values in the total count?