Introducing Radical.sh

Forget Code launches a powerful code generator for building API's

MERGE in Oracle

Consider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table.
MERGE INTO EMPLOYEES E
    USING HR_RECORDS H
    ON (E.ID = H.EMP_ID)
  WHEN MATCHED THEN
    UPDATE SET E.ADDRESS = H.ADDRESS
  WHEN NOT MATCHED THEN
    INSERT (ID, ADDRESS)
    VALUES (H.EMP_ID, H.ADDRESS);

The source can also be a query.
MERGE INTO EMPLOYEES E
    USING (SELECT * FROM HR_RECORDS WHERE START_DATE > ADD_MONTHS(SYSDATE, -1)) H
    ON (E.ID = H.EMP_ID)
  WHEN MATCHED THEN
    UPDATE SET E.ADDRESS = H.ADDRESS
  WHEN NOT MATCHED THEN
    INSERT (ID, ADDRESS)
    VALUES (H.EMP_ID, H.ADDRESS);


MERGE with multiple tables

MERGE INTO T1_MERGE T1
USING
(SELECT * FROM T2_MERGE T2 JOIN T3_MERGE T3 ON T2.ID2=T3.ID3) T2
ON
(T1.ID1=T2.ID2)
WHEN MATCHED THEN
UPDATE SET T1.NAME1='WOW'


MERGE with UPDATE and DELETE
MERGE INTO BONUSES D
   USING (SELECT EMPLOYEE_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES
   WHERE DEPARTMENT_ID = 80) S
   ON (D.EMPLOYEE_ID = S.EMPLOYEE_ID)
   WHEN MATCHED THEN UPDATE SET D.BONUS = D.BONUS + S.SALARY*.01
     DELETE WHERE (S.SALARY > 8000)
   WHEN NOT MATCHED THEN INSERT (D.EMPLOYEE_ID, D.BONUS)
     VALUES (S.EMPLOYEE_ID, S.SALARY*0.1)
     WHERE (S.SALARY <= 8000);