DML - Merge Statement di Oracle

Pada materi kali ini kita akan membahas Merge statement, perintah merge biasanya digunakan untuk update if exists and insert when not exists contoh penggunaanya seperti berikut:

Berikut adalah hasilnya:

SQL> merge into REGIONS rg
using (select &reg_id              as region_id,
              &reg_name as region_name
       from DUAL) du
on (rg.REGION_ID = du.region_id)
when matched then
    update
    set rg.REGION_NAME = du.region_name
    where rg.REGION_ID = du.region_id
when not matched then
    insert (REGION_ID, REGION_NAME)
    values (du.region_id, du.region_name); 

Enter value for reg_id: 5
Enter value for reg_name: 'South asian'

1 row merged.

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         5 South asian
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> /

Enter value for reg_id: 5
Enter value for reg_name: 'Asia Tenggara'

1 row merged.

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         5 Asia Tenggara
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

Back to top ↑