Recursive updates

Database
Enthusiast

Recursive updates

Hi,

I have a table with parent and child hierarchy. I need to frame an query to update an attribute in this table with respect to the value of its parent.

Parent Child Status 

A          B       Y

A          C       X

B          D       NULL

D          E       NULL

C           F       NULL

After Update : All the NULL's must be updated . The update value is decided upon  its parent status.

Parent Chuild Status 

A          B       Y

A          C       X

B          D       Y

D          E       Y

C           F       X

Tags (1)
1 REPLY
Junior Supporter

Re: Recursive updates

Hi,

The below query must do the required:

/*Creating test data*/
CREATE MULTISET VOLATILE TABLE VT_TEST_DATA ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
NO LOG
(
PARENT VARCHAR(1),
CHILD VARCHAR(1),
STATUS VARCHAR(1)
)
PRIMARY INDEX (PARENT,CHILD)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_TEST_DATA VALUES ('A','B','Y');
INSERT INTO VT_TEST_DATA VALUES ('A','C','X');
INSERT INTO VT_TEST_DATA VALUES ('B','D',NULL);
INSERT INTO VT_TEST_DATA VALUES ('D','E',NULL);
INSERT INTO VT_TEST_DATA VALUES ('C','F',NULL);
/*Completed creating test data*/

/*Your output*/
WITH RECURSIVE REC_CHILD_PARENT
(
PARENT
, CHILD
, STATUS
)
AS
(
SELECT
PARENT
, CHILD
, STATUS
FROM
VT_TEST_DATA
UNION ALL
SELECT
B.PARENT
, B.CHILD
, COALESCE(B.STATUS,A.STATUS) AS STATUS
FROM
REC_CHILD_PARENT A
JOIN
VT_TEST_DATA B
ON A.CHILD = B.PARENT
)
SELECT
DISTINCT
A.*
FROM
REC_CHILD_PARENT A
WHERE
STATUS IS NOT NULL
ORDER BY 1,2;

Please let me know in case of issues.

Thanks,

Rohan Sawant