前不久发现merge into原来还可以这么写：

drop table test purge;
drop table test_bak purge;

create table test as select * from user_objects where rownum <1000 order by object_name;
create table test_bak as select * from test where rownum <100 order by object_name;

merge into test a using(
 select test.object_id,test_bak.object_name from test,test_bak
  where test.object_type = 'TABLE'
    and test_bak.object_type = 'TABLE'
    and test.object_id = test_bak.object_id
    and test_bak.generated = 'N'
) b 
on (a.object_id = b.object_id) 
when matched then
 update set a.subobject_name = b.object_name;

原来可以改写成这样：
merge into (select * from test where object_type = 'TABLE') a
using (select * from test_bak
        where object_type = 'TABLE'
          and generated = 'N') b
on (a.object_id = b.object_id) 
when matched then
 update set a.subobject_name = b.object_name;