Search code examples
oracle-databasebulk

Oracle PL/SQL: Update table B with aggregation data of table A

Please suppose you have an Oracle table, TABLE A, as follows:

enter image description here

In this table the main fields are FIELD1 and FIELD2. You can see that:

a) For the couple (AAA, 1) we have two values: 200.03 and 100.02; b) For the couple (BBB, 3) we have two values: 300.04 and 400.05.

We would like to make a sum aggregation as follows, updating the following table:

enter image description here

In field3 of table B, we would like to store the sum of 200.03 and 100.02 with reference to the couple (AAA, 1), and we would like to store the sum of 300.04 and 400.05 with reference to the couple (BBB, 3).

Please imagine that we could have many different couples: (ZZZ, 77) (YYY, 12) ... and so on.

Please suppose that the record referred to a single couple may be more than two, in which case we should sum the values of all the records regarding the same couple.

In our simple case, the result will be the following:

enter image description here

The real case has a table A with about 20 million of records, so I would like to write the software in PL/SQL using BULK COLLECT, UPDATE and FORALL.

What would be the best approach? Please provide PL/SQL code in order to explain how to solve the problem.

Thank you very much for considering my request.

Solution

  • Frankly I wouldn't use BULK COLLECT and FORALL here - I'd use a MERGE statement. Try something like

    MERGE INTO TABLE_B b
  USING (SELECT FIELD1, FIELD2, SUM(FIELD3) AS TOTAL_FIELD3
           FROM TABLE_A
           GROUP BY FIELD1, FIELD2) a
    ON (b.FIELD1 = a.FIELD1 AND
        b.FIELD2 = a.FIELD2)
WHEN NOT MATCHED THEN
   INSERT (FIELD1, FIELD2, FIELD3)
     VALUES (a.FIELD1, a.FIELD2, a.TOTAL_FIELD3)
WHEN MATCHED THEN
   UPDATE
     SET FIELD3 = a.TOTAL_FIELD3;

    Best of luck.