Lesson Learned abt MERGE command
While helping out one of my colleague with his MERGE Command, We were misguided by wrong oracle error message for almost 1 hr.
The error was ORA-00904:invalid identifier.
Ultimately, I found that it was undocumented restriction that you cannot UPDATE any column that you are MERGING on or Joining on.
For Ex.
MERGE INTO emp e
USING emp_stage d
ON (e.empno = d.empno
e.deptid = d.deptid
)
WHEN MATCHED THEN UPDATE
SET e.deptid = d.deptid,
e.ename = d.ename,
e.sal = d.sal,
WHEN NOT MATCHED THEN
INSERT ( empno, deptid, ename, sal )
values ( d.empno,
d.deptid,
d.ename,
d.sal);
ERROR at line 4:
ORA-00904: "d"."deptid": invalid identifier
But if you remove deptid Join, It works.
MERGE INTO emp e
USING emp_stage d
ON (e.empno = d.empno
--e.deptid = d.deptid
)
WHEN MATCHED THEN UPDATE
SET e.deptid = d.deptid,
e.ename = d.ename,
e.sal = d.sal,
WHEN NOT MATCHED THEN
INSERT ( empno, deptid, ename, sal )
values ( d.empno,
d.deptid,
d.ename,
d.sal )
It was common sense, How would you update column on which you are making selection ? It is same as mutating table error, only it hasn't been documented yet. May be It should say:
ORA-99999:cannot UPDATE column that are MERGING on.
This will save lot of developers debugging time.