更新操作中的外关联（一）-CSDN博客

今天同事和我说，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;

有人也许会说，将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;

使用这种方法，虽然多关联了一次被更新表，但是由于更新通过主键完成，因此效率影响并不大，更重要的是，这种方法不会随着被更新字段增加而降低更新的性能。

那么有没有办法通过外连接的方式，只连接一次就达到更新的目的呢，如果改写一下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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-293562/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-293562/