SELECT X.MAT_SPEC_SEQ, X.DSN_SLAB_NO FROM (SELECT ROWNUM AS RN, A.MAT_SPEC_SEQ, A.DSN_SLAB_NO FROM TBD01_MATDN_RST3 A START WITH A.MAT_SPEC_SEQ = :1 CONNECT BY PRIOR A.MAT_SPEC_SEQ = A.PMAT_SPEC_SEQ ORDER BY 1 DESC) X DELETE FROM TBD01_MATDN_RST3 WHERE MAT_SPEC_SEQ = :1 UPDATE TBD01_SLABDN_OBJ2 SET PROC_STAT = :1 WHERE DSN_SLAB_NO = :2 SELECT * FROM (SELECT MAT_SPEC_SEQ, PMAT_SPEC_SEQ, WITHIN_SUB_SEQ , ROW_NUMBER() OVER (PARTITION BY PMAT_SPEC_SEQ ORDER BY WITHIN_SUB_SEQ) AS RNUM FROM TBD01_MATDN_RST3) A WHERE A.WITHIN_SUB_SEQ != A.RNUM START WITH A.PMAT_SPEC_SEQ = :1 CONNECT BY PRIOR A.MAT_SPEC_SEQ = A.PMAT_SPEC_SEQ UPDATE TBD01_MATDN_RST3 SET WITHIN_SUB_SEQ = :1 WHERE MAT_SPEC_SEQ = :2