更新 SELECT 语句的结果 [英] Update the results of a SELECT statement

问题描述

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

Oracle lets you update the results of a SELECT statement.

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

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

I suppose that this could be used for updating columns in one table based on the value of a matching row in another table.

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

How is this feature called, can it efficiently be used for large updates, does it work when the SELECT joins multiple tables, and if so, how?

推荐答案

我还没有看到这个的正式名称.Oracle SQL Reference 只是指更新子查询.我倾向于将其视为视图更新"的一种形式，子查询位于内联视图中.

I haven't seen a formal name for this. The Oracle SQL Reference just refers to updating a subquery. I tend to think of it as a form of "view updating", with the subquery being in in-line view.

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

Yes, it works when a number of tables are joined, but subject to the rules of view updating. This means that only one of the view's base tables can be updated, and this table must be "key-preserved" in the view: i.e. its rows should only be able to appear once in the view. This requires that any other tables in the view (subquery) are referenced via foreign key constraints on the table to be updated.

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

Some examples may help. Using the standard Oracle EMP and DEPT tables, with EMP.EMPNO being defined as the primary key of EMP, and EMP.DEPTNO being defined as a foreign key to DEPT.DEPTNO, then this update is allowed:

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);

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

As for performance: the optimiser will (must) identify the base table to be updated during parsing, and joins to other table will be ignored since they do not have any bearing on the update to be performed - as this AUTOTRACE output shows:

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).

(Note that table DEPT is never accessed even though DEPT.DNAME appears in the subquery).

这篇关于更新 SELECT 语句的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！