Oracle数据更新记录,Oracle 基于其他表中的数据更新记录时空值的处理方法

最新推荐文章于 2022-12-05 17:42:18 发布

协和临床营养科陈

最新推荐文章于 2022-12-05 17:42:18 发布

阅读量178

点赞数

文章标签： Oracle数据更新记录

--创建测试数据

--将 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行。