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

--------------------------------------------
S