I have a table where a family monthly expenditure is stored. In this table there have a parent-child relation. From here I want to get total amount of expenditure for the family. So how can I update TOTAL_AMOUNT column or do that thing?

For enquiry I give dummy table and insert script.

CREATE TABLE PARENT_CHILD
(
   ID             NUMBER (10),
   PARENT_ID      NUMBER (10),
   OWN_AMOUNT     NUMBER (20),
   TOTAL_AMOUNT   NUMBER (20)
);

INSERT INTO PARENT_CHILD VALUES (1, NULL, 100000, NULL);
INSERT INTO PARENT_CHILD VALUES (2, NULL, 15000, NULL);
INSERT INTO PARENT_CHILD VALUES (3, NULL, 10000, NULL);
INSERT INTO PARENT_CHILD VALUES (4, 3, 2000, NULL);
INSERT INTO PARENT_CHILD VALUES (5, NULL, 100000, NULL);
INSERT INTO PARENT_CHILD VALUES (6, 5, 25000, NULL);
INSERT INTO PARENT_CHILD VALUES (7, 6, 15000, NULL);
INSERT INTO PARENT_CHILD VALUES (8, 5, 30000, NULL);
INSERT INTO PARENT_CHILD VALUES (9, 5, 20000, NULL);
INSERT INTO PARENT_CHILD VALUES (10, 5, 8000, NULL);
INSERT INTO PARENT_CHILD VALUES (11, 10, 2000, NULL);

For example I want to update table like following:

Thanks in advance.

CodePudding user response：

As there are more than parent-child (but also grandchild and - possibly - even deeper) relationship, you'll need a recursive CTE to correctly say who is who and update the source table.

Something like this:

SQL> update parent_child pc set
  2    pc.total_amount =
  3      (with
  4          children (parent, child) as
  5            (select parent_id, id
  6             from parent_child
  7             union all
  8             select d.parent, a.id
  9             from children d join parent_child a on d.child = a.parent_id
 10            )
 11        select
 12          sum(a.own_amount) amount
 13        from children c join parent_child a on a.id = c.child
 14        where nvl(c.parent, c.child) = pc.id
 15        group by nvl(c.parent, c.child)
 16       );

11 rows updated.

Result:

SQL> select * from parent_child order by id;

        ID  PARENT_ID OWN_AMOUNT TOTAL_AMOUNT
---------- ---------- ---------- ------------
         1                100000       100000
         2                 15000        15000
         3                 10000        12000
         4          3       2000         2000
         5                100000       200000
         6          5      25000        40000
         7          6      15000        15000
         8          5      30000        30000
         9          5      20000        20000
        10          5       8000        10000
        11         10       2000         2000

11 rows selected.

SQL>

CodePudding user response：

You can use a correlated hierarchical query:

UPDATE parent_child p
SET total_amount = (SELECT SUM(own_amount)
                    FROM   parent_child s
                    START WITH s.ROWID = p.ROWID
                    CONNECT BY PRIOR id = parent_id);

Which, for the sample data, outputs:

fiddle