Merge Statement on same table

MERGE INTO CLEMAILDETAIL t2

USING dual

on (t2.firmnumber = :firm and t2.oowncode= :code)

WHEN MATCHED THEN

  UPDATE SET

        t2.cclientlevelpassword = :pass

;

MERGE INTO CLEMAILDETAIL t2

USING dual

on (t2.oowncode= '10002')

WHEN MATCHED THEN

  UPDATE SET

        t2.cclientlevelpassword = 'abc'

;

-------------for better index use -------------------

MERGE INTO CLEMAILDETAIL t2

USING

(

  SELECT firmnumber,oowncode       

  FROM CLEMAILDETAIL where oowncode='10002'

) t1

ON (t2.firmnumber = t1.firmnumber and t2.oowncode=t1.oowncode)

WHEN MATCHED THEN

  UPDATE SET

        t2.cclientlevelpassword = :password

;

eg.

MERGE INTO CLEMAILDETAIL t2

USING

(

  SELECT firmnumber,oowncode       

  FROM CLEMAILDETAIL where oowncode='10002'

) t1

ON (t2.firmnumber = t1.firmnumber and t2.oowncode=t1.oowncode)

WHEN MATCHED THEN

  UPDATE SET

        t2.cclientlevelpassword = 'abc'

;

MERGE INTO to tbltest i

USING (select 1 from dual) ii

ON (i.id = :id AND i.url = :url)

WHEN MATCHED THEN

UPDATE SET

title = :title

WHEN NOT MATCHED THEN

INSERT (i.id, i.url, i.title)

VALUES (:id, :url, :title)

MERGE INTO Employee USING dual ON ( "id"=20200714) WHEN MATCHED THEN UPDATE SET "last"="agarwal" , "name"="kshitij" WHEN NOT MATCHED THEN INSERT ("id","last","name") VALUES ( 20200714,"agarwal", "kshitij" )