更新 SELECT 语句的结果

Question

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

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

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

这个功能如何称呼，它是否可以有效地用于大型更新，当 SELECT 连接多个表时它是否有效？如果可以，如何？

Answer 1

感谢您的评论，我认为这是标准的 Sql...:(

对于 Oracle，您可以在表上编写更新，在其中使用连接检索信息，例如：

UPDATE (
    SELECT * 
    FROM table1 t1 
    LEFT JOIN table2 t2 ON t2.t1id = t1.ID
) SET t1.col1 = t2.col2

对于 Sql Server，它是：

UPDATE t1
SET col1 = t2.col2
FROM table1 t1
LEFT JOIN table2 t2 on t2.t1id = t1.id

如果有人知道可行的方法关于 Oracle、Sql Server 和 MySql 我很感兴趣。

Answer 2

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

是的，当多个表连接时它可以工作，但要遵守视图更新的规则。 这意味着只能更新视图的一个基表，并且该表必须在视图中“键保留”：即它的行只能在视图中出现一次。 这要求视图（子查询）中的任何其他表都通过要更新的表的外键约束进行引用。

一些例子可能会有所帮助。 使用标准的 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);

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

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 也永远不会被访问。

Answer 3

您提到的表格没有具体名称 AFAIK。 只是更新 select 语句的结果。

还有另一种形式称为相关更新（具有单列或多列更新）

UPDATE TABLE(<SELECT STATEMENT>) <alias>
SET <column_name> = (
  SELECT <column_name>
  FROM <table_name> <alias>
  WHERE <alias.table_name> <condition> <alias.table_name>
);

多列形式

...
SET (<column_name_list>) = (
  SELECT <column_name_list>
...

还有一种形式也可以从中返回值，称为使用返回子句更新

以及一些具体信息使用嵌套表进行更新。 最好至少检查这两页

Oracle ® 数据库 SQL 语言参考 SELECT

Oracle® 数据库 SQL 语言参考更新