2 Replies Latest reply: Nov 6, 2013 8:41 PM by Frank Kulash RSS

    Updating count data based on records in another table


      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

          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



            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;
                SELECT  GREATEST ( SYSDATE
                                 , 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     (     dst.name  = src.name
                       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.name, dst.item, dst.cnt)
                                      VALUES (src.name, 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: https://forums.oracle.com/message/9362002


            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.