With as 必须跟select

update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id);create table a as (select * from (select * from dba_objects order by object_id) where rownum<100)create table b as (select * from (select * from dba_objects order by object_id) where rownum<100)SQL> update b set object_name=object_name||'xxx';99 rows updated.SQL> select count(*) from a;  COUNT(*)----------99子查询会被扫描99次SQL> alter session set statistics_level=all;Session altered.SQL> update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id);99 rows updated.SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID9n2d868fqhcq4, child number 0-------------------------------------update b set b.object_name=(select a.object_name from a wherea.object_id=b.object_id)Plan hash value: 1917715316-------------------------------------------------------------------------------------| Id  | Operation   | Name | Starts | E-Rows | A-Rows | A-Time   | Buffers |-------------------------------------------------------------------------------------|   0 | UPDATE STATEMENT   |  | 1 |    |   0 |00:00:00.01 |504 ||   1 |  UPDATE    | B  | 1 |    |   0 |00:00:00.01 |504 ||   2 |   TABLE ACCESS FULL| B  | 1 | 99 |  99 |00:00:00.01 |  4 ||*  3 |   TABLE ACCESS FULL| A  |99 |  1 |  99 |00:00:00.01 |396 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("A"."OBJECT_ID"=:B1)Note-----   - dynamic sampling used for this statement (level=2)25 rows selected.可以看到A表被扫描了99次,类似于NL。改为with as 呢？with  A as (select a.object_name from a )update b set b.object_name=a.object_name where  a.object_id=b.object_id;SQL> with  A as (select a.object_name from a )update b set b.object_name=a.object_name where  a.object_id=b.object_id;  2    3  update b set b.object_name=a.object_name*ERROR at line 2:ORA-00928: missing SELECT keywordwith as 必须紧跟引用的select 语句,而不是delete update merge