SQL>
SQL>
SQL> create table table_a( id number );
Table created.
SQL> insert into table_a
2 select rownum from all_tables where rownum < 7;
6 rows created.
SQL>
SQL> select * from table_a;
ID
----------
1
2
3
4
5
6
6 rows selected.
SQL> create table table_b(
2 id number,
3 status varchar2(255) );
Table created.
SQL>
SQL> insert into table_b values( 1, 'NEW' );
1 row created.
SQL> insert into table_b values( 3, 'NEW' );
1 row created.
SQL> insert into table_b values( 5, 'NEW' );
1 row created.
SQL> select * from table_b;
ID STATUS
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 NEW
3 NEW
5 NEW
SQL> merge into table_b b using ( select * from table_a ) a
2 on ( a.id = b.id )
3 when matched then update set status = 'OLD'
4 when not matched then insert values ( a.id, 'NEW' );
6 rows merged.
SQL>
SQL> select * from table_b;
ID STATUS
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 OLD
3 OLD
5 OLD
6 NEW
4 NEW
2 NEW
6 rows selected.
SQL>
SQL> drop table table_a;
Table dropped.
SQL> drop table table_b;
Table dropped.