All Forums Database
sujiwarrier 19 posts Joined 11/13
14 Jan 2015
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:
Rohan_Sawant 55 posts Joined 07/14
14 Jan 2015

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

You must sign in to leave a comment.