更新操作中的外關聯（一）

今天同事和我說，UPDATE一個表的時候，這個表和其他表進行外關聯獲取更新資料無效。

 

 

問題比較有意思，下面構造一個簡單的例子來線上這個問題。

SQL> CREATE TABLE T_UPDATE
  2  (
  3   ID NUMBER PRIMARY KEY,
  4   FID NUMBER,
  5   NAME VARCHAR2(30),
  6   AGE NUMBER(3)
  7  );

表已建立。

SQL> INSERT INTO T_UPDATE VALUES (1, 1, 'A', 1);

已建立 1 行。

SQL> INSERT INTO T_UPDATE VALUES (2, NULL, 'B', 2);

已建立 1 行。

SQL> CREATE TABLE T_PRIMARY
  2  (
  3   ID NUMBER PRIMARY KEY,
  4   NAME VARCHAR2(30),
  5   AGE NUMBER(3)
  6  );

表已建立。

SQL> INSERT INTO T_PRIMARY VALUES (1, 'C', 10);

已建立 1 行。

SQL> COMMIT;

提交完成。

下面要根據T_PRIMARY表的內容去更新T_UPDATE表的內容，這樣的關聯更新是經常可能碰到的，不過這裡希望對無法關聯到的記錄也一起更新，因此使用了外關聯：

SQL> SELECT * FROM T_UPDATE;

        ID        FID NAME                                  AGE
---------- ---------- ------------------------------ ----------
         1          1 A                                       1
         2            B                                       2

SQL> SELECT * FROM T_PRIMARY;

        ID NAME                                  AGE
---------- ------------------------------ ----------
         1 C                                      10

SQL> UPDATE T_UPDATE A
  2  SET NAME =
  3   (
  4    SELECT NVL(B.NAME, 'NULL')
  5    FROM T_PRIMARY B
  6    WHERE A.FID = B.ID(+)
  7   );

已更新2行。

SQL> SELECT * FROM T_UPDATE;

        ID        FID NAME                                  AGE
---------- ---------- ------------------------------ ----------
         1          1 C                                       1
         2                                                    2

可以看到外關聯並沒有像想象中的那樣起作用。T_UPDATE表的NAME列被更新為空，說明外關聯並沒有關聯到結果。這個SQL和下面不帶外關聯的更新語句是等價的：

SQL> ROLLBACK;

回退已完成。

SQL> UPDATE T_UPDATE A
  2  SET NAME =
  3   (
  4    SELECT NVL(B.NAME, 'NULL')
  5    FROM T_PRIMARY B
  6    WHERE A.FID = B.ID
  7   );

已更新2行。

SQL> SELECT * FROM T_UPDATE;

        ID        FID NAME                                  AGE
---------- ---------- ------------------------------ ----------
         1          1 C                                       1
         2                                                    2

有人也許會說，將NVL操作放到查詢的括號外面就可以了，比如：

SQL> ROLLBACK;

回退已完成。

SQL> UPDATE T_UPDATE A
  2  SET NAME =
  3   NVL(
  4    (
  5     SELECT B.NAME
  6     FROM T_PRIMARY B
  7     WHERE A.FID = B.ID
  8    ), 'NULL');

已更新2行。

SQL> SELECT * FROM T_UPDATE;

        ID        FID NAME                                  AGE
---------- ---------- ------------------------------ ----------
         1          1 C                                       1
         2            NULL                                    2

確實這種方式是有效的，但是如果更新的欄位變成兩個：

SQL> ROLLBACK;

回退已完成。

SQL> UPDATE T_UPDATE A
  2  SET (NAME, AGE) =
  3   (
  4    SELECT NVL(B.NAME, 'NULL'), NVL(B.AGE, 0)
  5    FROM T_PRIMARY B
  6    WHERE A.FID = B.ID(+)
  7   );

已更新2行。

SQL> SELECT * FROM T_UPDATE;

        ID        FID NAME                                  AGE
---------- ---------- ------------------------------ ----------
         1          1 C                                      10
         2

如果要採用剛才將NVL放到查詢外面的方法，就無法透過一個關聯語句來完成，因為NVL無法處理多個欄位組成的結果集，這時SQL將被迫改為：

SQL> ROLLBACK;

回退已完成。

SQL> UPDATE T_UPDATE A
  2  SET NAME =
  3   NVL(
  4    (
  5     SELECT B.NAME
  6     FROM T_PRIMARY B
  7     WHERE A.FID = B.ID
  8    ), 'NULL'),
  9   AGE =
 10   NVL(
 11    (
 12     SELECT B.AGE
 13     FROM T_PRIMARY B
 14     WHERE A.FID = B.ID
 15    ), 0)
 16   ;

已更新2行。

SQL> SELECT * FROM T_UPDATE;

        ID        FID NAME                                  AGE
---------- ---------- ------------------------------ ----------
         1          1 C                                      10
         2            NULL                                    0

一方面是寫法比較麻煩，另一方面是效能會比較差，而且欄位越多，這種寫法就越複雜，效能也就越差。

由此看來UPDATE語句中和其他表的外連線還是有必要的，那麼是否外連線真的無法使用在UPDATE語句中內，其實也不是，最簡單的方法是將被更新表也放到關聯語句中去：

SQL> ROLLBACK;

回退已完成。

SQL> UPDATE T_UPDATE A
  2  SET (NAME, AGE) =
  3   (
  4    SELECT NVL(B.NAME, 'NULL'), NVL(B.AGE, 0)
  5    FROM T_PRIMARY B, T_UPDATE C
  6    WHERE C.FID = B.ID(+)
  7    AND A.ID = C.ID
  8   );

已更新2行。

SQL> SELECT * FROM T_UPDATE;

        ID        FID NAME                                  AGE
---------- ---------- ------------------------------ ----------
         1          1 C                                      10
         2            NULL                                    0

使用這種方法，雖然多關聯了一次被更新表，但是由於更新透過主鍵完成，因此效率影響並不大，更重要的是，這種方法不會隨著被更新欄位增加而降低更新的效能。

那麼有沒有辦法透過外連線的方式，只連線一次就達到更新的目的呢，如果改寫一下SQL，改為對外關聯結果的更新是可以實現這個目標的：

SQL> ROLLBACK;

回退已完成。

SQL> UPDATE
  2  (
  3   SELECT A.NAME A_NAME,
  4    A.AGE A_AGE,
  5    B.NAME B_NAME,
  6    B.AGE B_AGE
  7   FROM T_UPDATE A, T_PRIMARY B
  8   WHERE A.FID = B.ID(+)
  9  )
 10  SET A_NAME = NVL(B_NAME, 'NULL'),
 11   A_AGE = NVL(B_AGE, 0);

已更新2行。

SQL> SELECT * FROM T_UPDATE;

        ID        FID NAME                                  AGE
---------- ---------- ------------------------------ ----------
         1          1 C                                      10
         2            NULL                                    0