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