更新SELECT语句的结果

Oracle lets you update the results of a SELECT statement.

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

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.

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

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?

如何调用此功能,它是否可以有效地用于大型更新,当SELECT连接多个表时是否有效,如果是,如何?

3 个解决方案

#1

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.

我还没有看到这个正式名称。 Oracle SQL Reference仅涉及更新子查询。我倾向于将其视为“视图更新”的一种形式,子查询处于内联视图中。

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.

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

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:

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

But this is not:

但这不是:

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

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:

至于性能:优化器将(必须)识别在解析期间要更新的基表,并且将忽略与其他表的连接,因为它们对要执行的更新没有任何影响 - 因为此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")

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

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

#2

The form you mention has no specific name AFAIK. Just updating the result of a select statement.

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

There is another form called Correlated update (with single or multicolumn update)

还有另一种称为相关更新的表单(具有单列或多列更新)

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

The multicolumn form

多列形式

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

There is also a from which also returning of values called Update with returning clause

还有一个名为Update with returns子句的返回值

And some specifics for updates with nested tables. Best is to check at least this two pages

以及嵌套表更新的一些细节。最好是检查这两页

Oracle® Database SQL Language Reference SELECT

Oracle®数据库SQL语言参考SELECT

Oracle® Database SQL Language Reference UPDATE

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

#3

Thanks for comments, I thought this was standard Sql... :(

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

For Oracle you can write an update on a table where you retrieve information with a join like:

对于Oracle,您可以在表上编写更新,通过以下连接检索信息:

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

For Sql Server, it's:

对于Sql Server,它是:

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

If anyone knows a way to do this that works on Oracle, Sql Server and MySql I'd be interested.

如果有人知道这样做的方法适用于Oracle,Sql Server和MySql,我会感兴趣。

#1