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

MySQLMySQLi Database

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)
raja
Published on 24-Dec-2019 06:05:41
Advertisements