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:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

bye

TPD

Hi,

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:

DECLARE
   run_date  DATE;
BEGIN
    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
           )
    WHEN MATCHED THEN UPDATE
    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);

 

    COMMIT;

END;
/

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. 11.2.0.2.0).

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.