This discussion is archived
2 Replies Latest reply: Nov 6, 2013 6:41 PM by Frank Kulash RSS

Updating count data based on records in another table

972653 Newbie
Currently Being Moderated

I have 2 tables

Table 1
Name Item   Date
Jon  Apples  06/11/2013 00:30:00 hrs
Sam  Oranges
Nish Apples



Table 2 - Net count
Name Item Count
Nish Apples 10
Nish Oranges 17
Nish Banana
Sam Apples 10
Sam Oranges 1
Sam Bananas 1
Jon  Apples 8


I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.
Please guide how to achieve this using PL/SQl or something similar

  • 1. Re: Updating count data based on records in another table
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated

    You need a third table where you store the timestamp of the last run or an additional field in Table1 which tells if a record has been processed already. (or does the Date column  carry this information?).


    then you have to know if all possible combinations of Name and Item are already in Table2. If so a simple update would do the job. If not you should have a look at the merge statement:




  • 2. Re: Updating count data based on records in another table
    Frank Kulash Guru
    Currently Being Moderated



    It depends on how you know if a row has been "added after last run". If it's all the rows that have NULL in the dt column, then, as suggested above, then you could use MERGE like this:


       run_date  DATE;
                         , MAX (dt)    -- DATE is not a good column name
                                    + INTERVAL '1' SECOND
        INTO    run_date
        FROM    table_1;


       UPDATE  table_1
        SET     dt = run_date
        WHERE   dt  IS NULL;


            MERGE INTO  net_count   dst
        USING  (
                   SELECT    name, item
                   ,         COUNT (*)  AS cnt
                   FROM      table_1
                   WHERE     dt  = run_date
                   GROUP BY  name, item
               )                src
        ON     (  =
               AND   dst.item  = src.item
        SET    dst.cnt            -- COUNT is not a good column nae
                       = NVL (dst.cnt, 0) + src.cnt
        WHEN NOT MATCHED THEN INSERT (, dst.item, dst.cnt)
                              VALUES (, src.item, src.cnt);




    If you'd care to post a little sample date (CREATE TABLE and INSERT statements) for both tables, and the results you want from that data, then I could test this.

    Always say what version of Oracle you're using (e.g.

    See the forum FAQ:


    This assumes that name and item in table_1 are never NULL.  If either can be NULL, the same basic approach will still work, but the details are a little more complicated.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points