sql - 更新 SELECT 语句的结果

Question

Oracle 允许您更新 SELECT 语句的结果。

UPDATE (<SELECT Statement>)
SET <column_name> = <value>
WHERE <column_name> <condition> <value>;

我想这可以用于根据另一个表中匹配行的值更新一个表中的列。

这个特性是如何调用的，它能否有效地用于大型更新，它在 SELECT 连接多个表时是否工作，如果是，如何工作？

Answer 1

我还没有看到这个的正式名称。 Oracle SQL Reference只是指更新子查询。我倾向于将其视为“ View 更新”的一种形式，子查询处于内嵌 View 中。

是的，它在连接多个表时起作用，但受 View 更新规则的约束。这意味着只能更新 View 的基表之一，并且该表在 View 中必须是“键保留的”:即它的行应该只能在 View 中出现一次。这要求 View (子查询)中的任何其他表都通过要更新的表上的外键约束进行引用。

一些例子可能会有所帮助。使用标准的 Oracle EMP 和 DEPT 表，将 EMP.EMPNO 定义为 EMP 的主键，将 EMP.DEPTNO 定义为 DEPT.DEPTNO 的外键，则允许此更新:

update (select emp.empno, emp.ename, emp.sal, dept.dname
        from   emp join dept on dept.deptno = emp.deptno
       )
set sal = sal+100;

但这不是:

-- DEPT is not "key-preserved" - same DEPT row may appear
-- several times in view
update (select emp.ename, emp.sal, dept.deptno, dept.dname
        from   emp join dept on dept.deptno = emp.deptno
       )
set dname = upper(dname);

至于性能:优化器将(必须)在解析期间识别要更新的基表，并且将忽略与其他表的连接，因为它们对要执行的更新没有任何影响 - 正如这个 AUTOTRACE 输出所示:

SQL> update (select emp.ename, emp.sal, dept.dname
  2              from   emp join dept on dept.deptno = emp.deptno
  3             )
  4      set sal = sal-1;

33 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 1507993178

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |              |    33 |   495 |     3   (0)| 00:00:01 |
|   1 |  UPDATE             | EMP          |       |       |            |          |
|   2 |   NESTED LOOPS      |              |    33 |   495 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP          |    33 |   396 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0010666 |     1 |     3 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

(请注意，即使 DEPT.DNAME 出现在子查询中，表 DEPT 也不会被访问)。