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行。
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
--將 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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於JF框架中的空值處理框架
- vue中當資料為空時的處理Vue
- oracle中undo表空間丟失處理方法Oracle
- Oracle undo表空間爆滿的處理方法Oracle
- SQL Server資料庫中處理空值時常見問題SQLServer資料庫
- Oracle Temp臨時表空間處理Oracle
- oracle 表空間 不足時如何處理Oracle
- oracle臨時表空間過大的原因&&處理Oracle
- 影像處理或其他多媒體處理中的值溢位處理
- SAP CRM中介軟體下載時資料庫表CRMATAB為空的處理方法資料庫
- oracle實驗記錄 （恢復-表空間基於時間點恢復(rman)）Oracle
- 6 關於資料倉儲維度資料處理的方法探究系列——緩慢變化維處理——記錄最新記錄及上
- 關於丟失表空間資料檔案的處理方式
- JMeter中對於Json資料的處理方法JMeterJSON
- 基於Spark的大資料實時處理開課Spark大資料
- 基於MFC的大型資料檔案處理方法 (轉)
- oracle實驗記錄 （恢復-表空間基於時間點恢復(手動)）Oracle
- 影像資料不足時的處理方法
- undo 表空間滿了的處理方法
- Oracle中如何更新一張大表記錄Oracle
- Oracle RAC 錯誤記錄以及處理方法Oracle
- ORACLE&MYSQL同樣的記錄值進行編號處理OracleMySql
- 基於ORM思想的資料庫處理ORM資料庫
- 5 關於資料倉儲維度資料處理的方法探究系列——緩慢變化維處理——全歷史記錄
- Oracle中處理空值的函式nvl-nvl2-lnnvl-nullif的用法Oracle函式Null
- UNDO表空間下的資料檔案被誤刪除後的處理方法
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- 表空間資料檔案故障處理
- 檢視Oracle表中的指定記錄在資料檔案中的位置Oracle
- Oracle 排序中常用的NULL值處理方法Oracle排序Null
- 將ORACLE資料庫審計相關的表移動到其他表空間Oracle資料庫
- oracle快速向表中插入記錄方法Oracle
- 偽造基於ASSM表空間的資料塊SSM
- 關於oracle表空間的規劃方法Oracle
- BI系統中 關於oracle表空間的規劃方法Oracle
- Oracle排程作業引起的空間驟增問題處理記錄Oracle
- ORACLE資料庫壞塊的處理 (處理無物件壞快的方法)Oracle資料庫物件
- sysaux 表空間爆滿處理方法UX