MySQL query to update only a single field in place of NULL

For this, you can use COALESCE(). Let us first create a table −

mysql> create table DemoTable1805
     (
     Name1 varchar(20),
     Name2 varchar(20)
     );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1805 values('Chris',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1805 values('David','Mike');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1805 values(NULL,'Mike');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1805;

This will produce the following output −

+-------+-------+
| Name1 | Name2 |
+-------+-------+
| Chris | NULL  |
| David | Mike  |
| NULL  | Mike  |
+-------+-------+
3 rows in set (0.00 sec

Here is the query to update only one field −

mysql> update DemoTable1805
     set Name1 = coalesce(Name1,Name2);
Query OK, 1 row affected (0.00 sec)
Rows matched: 3  Changed: 1 Warnings: 0

Let us check the table records once again −

mysql> select * from DemoTable1805;

This will produce the following output −

+-------+-------+
| Name1 | Name2 |
+-------+-------+
| Chris | NULL  |
| David | Mike  |
| Mike  | Mike  |
+-------+-------+
3 rows in set (0.00 sec)

AmitDiwan

Updated on 24-Dec-2019 06:06:12

• Related Questions & Answers
• Only update the MySQL field if the field contains null or 0?
• MySQL query to fetch only a single field on the basis of boolean value in another field
• Update only a single column value in MySQL
• Update only the int in MySQL Field
• MySQL update multiple records in a single query?
• How to run MongoDB query to update only a specific field value?
• MySQL query to update only month in date?
• Update two columns with a single MySQL query
• A single MySQL query to update only specific records in a range without updating the entire column
• How do I update NULL values in a field in MySQL?
• MySQL CASE statement to place custom values in place of NULL
• How to bulk update MySQL data with a single query?
• Update only a single document in MongoDB
• Count boolean field values within a single MySQL query?
• MongoDB query for a single field