Application Design and Tuning for Performance › Avoiding Redundant Statements › Multi-Row Operations › MERGE Statement › Employee Sample Table

Employee Sample Table

The following example shows a merge of rows on the employee sample table:

MERGE INTO EMP AS EXISING_TBL
USING (VALUES (:EMPNO, :SALARY, :COMM, :BONUS)
       FOR :ROW-CNT ROWS) AS INPUT_TBL(EMPNO, SALARY, COMM, BONUS)
ON INPUT_TBL.EMPNO = EXISTING_TBL.EMPNO
WHEN MATCHED THEN
   UPDATE SET SALARY =  INPUT_TBL.SALARY
             ,COMM   =  INPUT_TBL.COMM
             ,BONUS  =  INPUT_TBL.BONUS
WHEN NOT MATCHED THEN
   INSERT (EMPNO, SALARY, COMM, BONUS)
   VALUES (INPUT_TBL.EMPNO, INPUT_TBL.SALARY, INPUT_TBL.COMM,
           INPUT_TBL.BONUS)