CODEWITHSUNDEEP
sqloracle-databasehierarchical-data
user1614862
user1614862

Reputation: 4159

How to update all children records of an hierarchical data structure in oracle

I have categories and groupings stored in an hierarchical order in a database. Each category may have another categories and groupings as it's children. So whenever I move a grouping to under another category or groupings it's category id will be updated and all it's children grouping's category id also should get updated.

The GROUPING table has the data as follows:

enter image description here

So, if I move grouping id 81 to a new category id 739720 then I want all children grouping's category id also to be updated to 739720.

in other words, I want to select all children records of a grouping in an hierarchical data structure and update their category ids.

I want data to be like below after the UPDATE.

enter image description here

Upvotes: 0

Views: 377

Answers (1)

MT0
MT0

Reputation: 168598

You can use a MERGE statement:

MERGE INTO GROUPING dst
USING (
  SELECT ROWID  AS rid
  FROM   GROUPING
  START WITH grouping_id = 81
  CONNECT BY PRIOR grouping_id = parent_id
) src
ON ( dst.ROWID = src.RID )
WHEN MATCHED THEN
  UPDATE SET CATEGORY_ID = 739720;

Upvotes: 1

Related Questions