--创建测试数据 --将 employee_id 为 100 的 department_id 更新为空
SQL> create table employees as select * from hr.employees;
SQL> update employees set department_id=NULL where employee_id=100;
SQL> select * from employees;
EMPLOYEE_ID FIRST_NAM LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- --------- --------- --------- ------------------------- -------------------- ---------- ------ -------------- ---------- -------------
100 Steven King SKING 515.123.4567 2003-06-17 00:00:00 AD_PRES 24000 101 Neena Kochhar NKOCHHAR 515.123.4568 2005-09-21 00:00:00 AD_VP 17000 100 90
102 Lex De Haan LDEHAAN 515.123.4569 2001-01-13 00:00:00 AD_VP 17000 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 00:00:00 IT_PROG 9000 102 60
104 Bruce Ernst BERNST 590.423.4568 2007-05-21 00:00:00 IT_PROG 6000 103 60
105 David Austin DAUSTIN 590.423.4569 2005-06-25 00:00:00 IT_PROG 4800 103 60
106 Valli Pataballa VPATABAL 590.423.4560 2006-02-05 00:00:00 IT_PROG 4800 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 2007-02-07 00:00:00 IT_PROG 4200 103 60
108 Nancy Greenberg NGREENBE 515.124.4569 2002-08-17 00:00:00 FI_MGR 12008 101 100
109 Daniel Faviet DFAVIET 515.124.4169 2002-08-16 00:00:00 FI_ACCOUNT 9000 108 100
110 John Chen JCHEN 515.124.4269 2005-09-28 00:00:00 FI_ACCOUNT 8200 108 100
--需求是：把所有员工的薪水修改成高于所属部门平均薪水的 10% --如果不对空值进行处理，直接更新的话，则 department_id 为空值的薪水会被 更新成空值。因为在这种场景下，一个值为 NULL 的部门无法与另一个值为 NULL 的部门匹配， 所以关联子查询会返回 NULL 。 SQL> update employees e
2 set salary =
3 (select avg(salary) * 1.10
4 from employees se
5 where se.department_id = e.department_id);
已更新107行。
SQL> select * from employees;
EMPLOYEE_ID FIRST_NAM LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- --------- --------- --------- ------------------------- -------------------- ---------- ------ -------------- ---------- -------------
100 Steven King SKING 515.123.4567 2003-06-17 00:00:00 AD_PRES 101 Neena Kochhar NKOCHHAR 515.123.4568 2005-09-21 00:00:00 AD_VP 18700 100 90
102 Lex De Haan LDEHAAN 515.123.4569 2001-01-13 00:00:00 AD_VP 18700 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 00:00:00 IT_PROG 6336 102 60
104 Bruce Ernst BERNST 590.423.4568 2007-05-21 00:00:00 IT_PROG 6336 103 60
105 David Austin DAUSTIN 590.423.4569 2005-06-25 00:00:00 IT_PROG 6336 103 60
106 Valli Pataballa VPATABAL 590.423.4560 2006-02-05 00:00:00 IT_PROG 6336 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 2007-02-07 00:00:00 IT_PROG 6336 103 60
108 Nancy Greenberg NGREENBE 515.124.4569 2002-08-17 00:00:00 FI_MGR 9461 101 100
解决方法： 1、在 WHERE 字句中添加一个非空条件 SQL> update employees e
2 set salary =
3 (select avg(salary) * 1.10
4 from employees se
5 where se.department_id = e.department_id)
6
where department_id is not null; 已更新105行。
SQL> select * from employees;
EMPLOYEE_ID FIRST_NAM LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- --------- --------- --------- ------------------------- -------------------- ---------- ------ -------------- ---------- -------------
100 Steven King SKING 515.123.4567 2003-06-17 00:00:00 AD_PRES 24000
101 Neena Kochhar NKOCHHAR 515.123.4568 2005-09-21 00:00:00 AD_VP 18700 100 90
102 Lex De Haan LDEHAAN 515.123.4569 2001-01-13 00:00:00 AD_VP 18700 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 00:00:00 IT_PROG 6336 102 60
104 Bruce Ernst BERNST 590.423.4568 2007-05-21 00:00:00 IT_PROG 6336 103 60
105 David Austin DAUSTIN 590.423.4569 2005-06-25 00:00:00 IT_PROG 6336 103 60
106 Valli Pataballa VPATABAL 590.423.4560 2006-02-05 00:00:00 IT_PROG 6336 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 2007-02-07 00:00:00 IT_PROG 6336 103 60
108 Nancy Greenberg NGREENBE 515.124.4569 2002-08-17 00:00:00 FI_MGR 9461 101 100
2、使用 NVL 函数来处理 NULL 值 SQL> update employees e
2 set salary =
3
nvl((select avg(salary) * 1.10
4 from employees se
5 where se.department_id = e.department_id),salary);
已更新107行。
SQL> select * from employees;
EMPLOYEE_ID FIRST_NAM LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- --------- --------- --------- ------------------------- -------------------- ---------- ------ -------------- ---------- -------------
100 Steven King SKING 515.123.4567 2003-06-17 00:00:00 AD_PRES 24000
101 Neena Kochhar NKOCHHAR 515.123.4568 2005-09-21 00:00:00 AD_VP 18700 100 90
102 Lex De Haan LDEHAAN 515.123.4569 2001-01-13 00:00:00 AD_VP 18700 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 00:00:00 IT_PROG 6336 102 60
104 Bruce Ernst BERNST 590.423.4568 2007-05-21 00:00:00 IT_PROG 6336 103 60
105 David Austin DAUSTIN 590.423.4569 2005-06-25 00:00:00 IT_PROG 6336 103 60
106 Valli Pataballa VPATABAL 590.423.4560 2006-02-05 00:00:00 IT_PROG 6336 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 2007-02-07 00:00:00 IT_PROG 6336 103 60
108 Nancy Greenberg NGREENBE 515.124.4569 2002-08-17 00:00:00 FI_MGR 9461 101 100
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2015795/，如需转载，请注明出处，否则将追究法律责任。