Hello All ,
Ran into a problem using MERGE statement . Looks like the USING clause in merge statement has to return data for the merge to work. My issue is that when the using select is not returning any data the merge does not perform the WHEN NOT MATCHED insert operation. Is it the default behavior or am i missing something . Also is there a way to overcome it. Here is what i am doing
DELETE FROM EMP WHERE EMPNO = 1;
MERGE INTO EMP A
USING (SELECT * FROM EMP WHERE EMPNO = 1) B
ON (A.EMPNO = B.EMPNO)
WHEN NOT MATCHED THEN
INSERT
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES
(1, 'S', 'S', 23, SYSDATE, 12, '', 34);
Commit;
In the above example i am expecting the row to be inserted but it is NOT . My actual business requirement is that my using clause will be based in input parameters and if no rows returned i need to insert else if match found update. Please clarify.