how to replace NULL value during insertion in sql

Question

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

+--------+--------+--------------+--------------+
| 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.

thanks in advance

Alter table and add a default value to that column. Will work if column value not passed, but not if NULL is explicitly specified. — jarlh
– jarlh, Commented Feb 4, 2016 at 9:44
In addition to the answers given below, you could also handle NULL values in your app layer, and replace them there with appropriate substitutes. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 4, 2016 at 9:48

Haytem BrB · Accepted Answer · 2016-02-04 10:04:16Z

2

For MySQL use:

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 )

answered Feb 4, 2016 at 10:04

Haytem BrB

1,5093 gold badges16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thanga · Accepted Answer · 2016-02-04 09:48:47Z

1

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

 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

edited Feb 4, 2016 at 9:48

answered Feb 4, 2016 at 9:41

Thanga

8,2003 gold badges22 silver badges39 bronze badges

Comments

sakthi sudhan · Accepted Answer · 2016-11-28 08:50:50Z

1

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

answered Nov 28, 2016 at 8:50

sakthi sudhan

1751 silver badge7 bronze badges

Comments

Roby Samuel · Accepted Answer · 2016-02-04 09:44:40Z

0

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

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

answered Feb 4, 2016 at 9:44

Roby Samuel

212 bronze badges

3 Comments

Jeeva Balan Over a year ago

It shows an error Error Code: 1582. Incorrect parameter count in the call to native function 'ISNULL'

Roby Samuel Over a year ago

try changing ISNULL to IFNULL. what database you're using?

Jeeva Balan Over a year ago

i am using mysql only

Collectives™ on Stack Overflow

how to replace NULL value during insertion in sql

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related