Merge into a table : Insert with subquery « Insert Delete Update « Oracle PL / SQL

1. Oracle PL / SQL
2. Insert Delete Update
3. Insert with subquery

Merge into a table

    
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.

   
    
    
  

Related examples in the same category