Oracle’da merge kelimesini kullanarak tek sql ile kayıt varsa güncelleme yoksa ekleme yaptırabiliriz (Upsert).
Upsert Sql’imiz:
merge into tablo t
using dual s on (t.alan1=1)
when matched then
update set alan2='UPDATE'
when not matched then
insert (alan1,alan2) values (1,'INSERT');
Test ediyoruz :
SQL> create table tablo (alan1 number(10),alan2 varchar2(10));
Table created
SQL> select * from tablo;
ALAN1 ALAN2
----------- ----------
SQL> merge into tablo t
2 using dual s on (t.alan1=1)
3 when matched then
4 update set alan2='UPDATE'
5 when not matched then
6 insert (alan1,alan2) values (1,'INSERT');
Done
SQL> select * from tablo;
ALAN1 ALAN2
----------- ----------
1 INSERT
SQL> merge into tablo t
2 using dual s on (t.alan1=1)
3 when matched then
4 update set alan2='UPDATE'
5 when not matched then
6 insert (alan1,alan2) values (1,'INSERT');
Done
SQL> select * from tablo;
ALAN1 ALAN2
----------- ----------
1 UPDATE
SQL> commit;
Commit complete
SQL>