Wednesday, November 15, 2006

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.