Merge into Duncs Tabular Form

Duncs has created an excellent JS based method for generating tabular forms in Apex. The nice part is that additional rows can be added without the need to refresh the page. I try to avoid using separate insert, update and delete statements and prefer the more performant Merge statement.

Duncs method

Demo

The issue is identifying the rows to be deleted in the UPDATE part of the MERGE as they are deleted from the collection and not just flagged for delete. The answer is to use an outer join to join your update table with your tabular form collection. My first few attempts resulted in syntax errors, so after trying various combinations of inline views, I came up with the following:

MERGE INTO emp e
USING (SELECT *
FROM (SELECT to_number(c001) c001
     ,c002 ename
     ,c003 job
     ,c004 mgr
     ,c005 hiredate
     ,f.empno
     ,f.deptno
   FROM (SELECT *
FROM apex_collections
WHERE collection_name = 'EMP')
FULL OUTER JOIN emp f ON to_number(c001) = f.empno)
WHERE deptno = :p1_deptno) c
ON (e.empno = c.empno)
WHEN MATCHED THEN
UPDATE
SET e.ename = c.ename
   ,e.job = c.job
   ,e.mgr = c.mgr
   ,e.hiredate = c.heirdate DELETE
WHERE c.c001 IS NULL
WHEN NOT MATCHED THEN
INSERT
   (e.ename
   ,e.job
   ,e.mgr
   ,e.heirdate
   ,e.deptno)
VALUES
   (c.ename
   ,c.job
   ,c.mgr
   ,c.hiredate
   ,:p1_deptno);

Comments

Anonymous said…
Simon,
Nice job, imaginative use of merge.
Bob R