前不久发现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;