如何在sql中插入时替换NULL值

my table looks like this

我的桌子看起来像这样

+--------+--------+--------------+--------------+
| CPI_id | Weight | score_100_UB | score_100_LB |
+--------+--------+--------------+--------------+
|    1.1 |     10 |          100 |           90 |
+--------+--------+--------------+--------------+

while executing the insert query the table should look like

在执行insert查询时,表应该是这样的

+--------+--------+--------------+--------------+
| CPI_id | Weight | score_100_UB | score_100_LB |
+--------+--------+--------------+--------------+
|    1.1 |     10 |          100 |           90 |
|    5.5 |     10 |         NULL |           93 |
+--------+--------+--------------+--------------+

but NULL values should be replaced by 100. I also tried using trigger.I couldn't get.

但是NULL值应该替换为100.我也尝试使用trigger.I无法获取。

thanks in advance

提前致谢

4 个解决方案

#1

For MySQL use:

对于MySQL使用:

insert into table values (CPI_id , Weight ,IFNULL(score_100_UB ,100), score_100_LB )

or:

insert into table values (CPI_id , Weight ,COALESCE(score_100_UB ,100), score_100_LB )

SQL Server:

insert into table values (CPI_id , Weight ,ISNULL(score_100_UB ,100), score_100_LB )

Oracle:

insert into table values (CPI_id , Weight ,NVL(score_100_UB ,100), score_100_LB )

#2

select E.emp_name as Employee, ISNULL( M.emp_name,'No Manager') as Manager
from Employee_Test_salary4 E
left join Employee_Test_salary4 M
on E.report_manager = M.report_manager

or

select E.emp_name as Employee, COALESCE( M.emp_name,'No Manager') as Manager
from Employee_Test_salary4 E
left join Employee_Test_salary4 M
on E.report_manager = M.report_manager

or

select E.emp_name as Employee, CASE WHEN  M.emp_name IS NULL THEN 'No Manager' ELSE M.emp_name END as Manager
from Employee_Test_salary4 E
left join Employee_Test_salary4 M
on E.report_manager = M.report_manager

#3

if you're using a query then use ISNULL() function

如果您正在使用查询,则使用ISNULL()函数

insert into table values (CPI_id , Weight ,ISNULL(score_100_UB ,100), score_100_LB )

#4

Alter your table and set the field score_100_UB to have some default value like below

更改您的表并将字段score_100_UB设置为具有如下所示的默认值

 ALTER TABLE t1 MODIFY score_100_UB INT UNSIGNED DEFAULT 100;

After this, whenever you try to insert a NULL value in this column, it will be replaced by 100

在此之后,每当您尝试在此列中插入NULL值时,它将替换为100

#1