I'm using a closure table to manage an organisation structure. The organisation table is defined as:

CREATE TABLE ORGANISATION 

(

  ORGID DOM_INTLARGE NOT NULL,

  ORG_NME DOM_VARCHARMEDIUM NOT NULL,

  LEVEL_NUM DOM_INTSMALL NOT NULL,

  INUSE DOM_BOOLN,

CONSTRAINT PK_ORGANISATION PRIMARY KEY (ORGID),

CONSTRAINT UN_ORGNME UNIQUE (ORG_NME, LEVEL_NUM)

);

and the closure table as:

CREATE TABLE ORGCHART 

(

  PARENTID DOM_INTLARGE NOT NULL,

  CHILDID DOM_INTLARGE NOT NULL,

  DEPTH DOM_INTSMALL NOT NULL,

CONSTRAINT PK_ORGCHART PRIMARY KEY (PARENTID, CHILDID),

CONSTRAINT FK_ORG2CHCHILD FOREIGN KEY (CHILDID) REFERENCES ORGANISATION (ORGID),

CONSTRAINT ORG2CHPARENT FOREIGN KEY (PARENTID) REFERENCES ORGANISATION (ORGID));

If I use the statements in the following stored procedure to move a part in the middle of the structure (where DEPTH =2 in the ORGCHART table) I get a PRIMARY or UNIQUE KEY constraint "PK_ORGCHART" constraint error as the delete statement hasn't removed all the required rows.

SET TERM ^ ;

CREATE PROCEDURE MOVEORGANISATION_OLD 

(

  IN_ORGTOMOVE BIGINT,

  IN_WHERETOMOVE BIGINT

)

AS

BEGIN 

DELETE FROM orgchart

WHERE childid IN (SELECT childid

FROM OrgChart

WHERE parentid = :IN_OrgToMove)

AND parentid IN (SELECT parentid

FROM OrgChart

WHERE childid = :IN_OrgToMove

AND parentid != childid);

INSERT INTO OrgChart (ParentId, ChildId, depth)

SELECT supertree.ParentId, subtree.ChildId, subtree.DEPTH + supertree.DEPTH + 1

FROM OrgChart supertree

CROSS JOIN OrgChart  subtree

WHERE supertree.ChildId = :IN_WhereToMove

AND subtree.ParentId = :IN_OrgToMove;

END

 ^

SET TERM ; ^

However if I first store a list of the rows to be delete from ORGCHART table into a temporary table defined as:

CREATE TABLE TMP_ORGCHANGE 

(

  ORGID DOM_INTLARGE,

  PARENTID DOM_INTLARGE

);

and then use it in the where clause criteria of the delete statement everything works as expected (see revise procedure below).

SET TERM ^ ;

CREATE PROCEDURE MOVEORGANISATION 

(

  IN_ORGTOMOVE BIGINT,

  IN_WHERETOMOVE BIGINT

)

AS

BEGIN

/*List records to be removed into temporary table */

insert into TMP_ORGCHANGE (parentid, orgid)

select parentid, childid FROM orgchart

WHERE childid IN (SELECT childid

FROM OrgChart

WHERE parentid = :IN_OrgToMove)

AND parentid IN (SELECT parentid

FROM OrgChart

WHERE childid = :IN_OrgToMove

AND parentid != childid);

/* now delete records  from ORGCHART */

delete from ORGCHART

where parentid in (select parentid from TMP_ORGCHANGE)

and   childid in (select orgid from TMP_ORGCHANGE);

/* create the new links */

INSERT INTO OrgChart (ParentId, ChildId, depth)

SELECT supertree.ParentId, subtree.ChildId, subtree.DEPTH + supertree.DEPTH + 1

FROM OrgChart supertree

CROSS JOIN OrgChart  subtree

WHERE supertree.ChildId = :IN_WhereToMove

AND subtree.ParentId = :IN_OrgToMove;

END

 ^

SET TERM ; ^

Is there something that I'm doing wrong in the first procedure that prevents all relevant records from being deleted.

Here is a sample dataset to illustrate. If I pass the values 5,2 to the first procedure the operation fails but passing them to the second procedure it succeeds. 

INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES ('1', 'Whole Organisation', '0', NULL, NULL);

INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES ('2', 'Div1', '1', '1', '1');

INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES ('3', 'Div2', '1', '1', '1');

INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES ('4', 'Dir11', '2', '2', '1');

INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES ('5', 'Dir21', '2', '3', '1');

INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES ('6', 'Spec111', '3', '4', '1');

INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES ('7', 'Spec112', '3', '4', '1');

INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES ('8', 'Spec211', '3', '5', '1');

INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES ('9', 'Spec212', '3', '5', '1');