How to insert a value at a NULL position in a column using the MySQL COALESCE() function?-Mysql Tutorial-php.cn

How to insert a value at a NULL position in a column using the MySQL COALESCE() function?

王林

Release： 2023-09-15 15:01:02

forward

878 people have browsed it

如何使用 MySQL COALESCE() 函数在列中的 NULL 位置插入值？

To understand it, we are using data from table "Employee" with Salary=NULL for ID = 5 and 6 as follows-

mysql> Select * from Employee;
+----+--------+--------+
| ID | Name   | Salary |
+----+--------+--------+
| 1  | Gaurav | 50000  |
| 2  | Rahul  | 20000  |
| 3  | Advik  | 25000  |
| 4  | Aarav  | 65000  |
| 5  | Ram    | NULL   |
| 6  | Mohan  | NULL   |
+----+--------+--------+
6 rows in set (0.00 sec)

Copy after login

Now , the following query will use the COALESCE() function with the UPDATE and WHERE clauses to place the value in the NULL position.

mysql> Update Employee set Salary = COALESCE(Salary,20000) where Id = 5;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> Update Employee set Salary = COALESCE(Salary,30000) where Id = 6;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> Select * from Employee;
+----+--------+--------+
| ID | Name   | Salary |
+----+--------+--------+
| 1  | Gaurav | 50000  |
| 2  | Rahul  | 20000  |
| 3  | Advik  | 25000  |
| 4  | Aarav  | 65000  |
| 5  | Ram    | 20000  |
| 6  | Mohan  | 30000  |
+----+--------+--------+
6 rows in set (0.00 sec)

Copy after login

The above is the detailed content of How to insert a value at a NULL position in a column using the MySQL COALESCE() function?. For more information, please follow other related articles on the PHP Chinese website!