Merge Statement - Upsert (Update + Insert)

MERGE is a DML command that enables us to optionally update or insert data into a target table, depending on whether matching records already exist.

Functionality of MERGE:

For every_record_in TARGET TABLE
loop
   IF (matching record exists in SOURCE_DATA) then
       Update the record in target table;
   ELSE
       Insert new record in target table;
    END IF;
End loop;




EXAMPLE:


Source_daily : This table gets populated by some process on daily basis. The table will have day_date and 3 block usages per day.


Target_monthly: This tables should have a record per month per year. The block usage must be the sum of block usages of all the days (source_daily) for each month.


Logic flow:
On first day of every month a new record must be created in Target_monthly for that particular month & this record will have the block usage for day 1.
On second day, the record must be the same as day 1 record but the block usage must be updated to sum of day 1 and day 2.
On third day, the record must be the same as day 1_2 record but the block usage must be updated to sum of
day 1 , day 2 and day 3.
The above process till end of the month.
Next month a New record will be inserted to Target_monthly table & the same aggregation continues......


CODE DEMO:

Create table source_daily
(day_date date primary key,
 block_1 number(6,2),
 block_2 number(6,2),
 block_3 number(6,2)
);

Insert into source_daily values (sysdate,10,20,30);

Commit;

Create table target_monthly
(Month_year number primary key,
 block_1 number(6,2),
 block_2 number(6,2),
 block_3 number(6,2)
);


Merge into target_monthly tgt
using
       (
         select to_char(day_date,'MMYYYY') month_year,
                   sum(block_1) total_block_1,
                   sum(block_2) total_block_2,
                   sum(block_3) total_block_3 
        from source_daily 
        group by to_char(day_date,'MMYYYY')
       ) src

on ( src.month_year=tgt.Month_year)

when matched
then
update
set
tgt.block_1=src.total_block_1,
tgt.block_2=src.total_block_2,
tgt.block_3=src.total_block_3

when not matched
then
insert
(tgt.month_year,
 tgt.block_1,
 tgt.block_2,
 tgt.block_3)
values
( src.month_year,
  src.total_block_1,
  src.total_block_2,
  src.total_block_3);

commit;

select * from target_monthly;


Insert into source_daily values (sysdate+1,15,25,35);

commit;

Merge into target_monthly tgt

using 

       (

         select to_char(day_date,'MMYYYY') month_year,

                   sum(block_1) total_block_1,

                   sum(block_2) total_block_2,

                   sum(block_3) total_block_3 

        from source_daily 

        group by to_char(day_date,'MMYYYY')

       ) src

on ( src.month_year=tgt.Month_year)

when matched

then

update

set 

tgt.block_1=src.total_block_1,

tgt.block_2=src.total_block_2,

tgt.block_3=src.total_block_3

when not matched

then

insert 

(tgt.month_year,

 tgt.block_1,

 tgt.block_2,

 tgt.block_3)

values

( src.month_year,

  src.total_block_1,

  src.total_block_2,

  src.total_block_3);

Commit;

Insert into source_daily values (sysdate+2,20,30,40);

Commit;

Merge into target_monthly tgt

using 

       (

         select to_char(day_date,'MMYYYY') month_year,

                   sum(block_1) total_block_1,

                   sum(block_2) total_block_2,

                   sum(block_3) total_block_3 

        from source_daily 

        group by to_char(day_date,'MMYYYY')

       ) src

on ( src.month_year=tgt.Month_year)

when matched

then

update

set 

tgt.block_1=src.total_block_1,

tgt.block_2=src.total_block_2,

tgt.block_3=src.total_block_3

when not matched

then

insert 

(tgt.month_year,

 tgt.block_1,

 tgt.block_2,

 tgt.block_3)

values

( src.month_year,

  src.total_block_1,

  src.total_block_2,

  src.total_block_3);

Commit;
-- Next month record
Insert into source_daily values (last_day(sysdate)+1,10,20,30);

commit;

Merge into target_monthly tgt

using 

       (

         select to_char(day_date,'MMYYYY') month_year,

                   sum(block_1) total_block_1,

                   sum(block_2) total_block_2,

                   sum(block_3) total_block_3 

        from source_daily 

        group by to_char(day_date,'MMYYYY')

       ) src

on ( src.month_year=tgt.Month_year)

when matched

then

update

set 

tgt.block_1=src.total_block_1,

tgt.block_2=src.total_block_2,

tgt.block_3=src.total_block_3

when not matched

then

insert 

(tgt.month_year,

 tgt.block_1,

 tgt.block_2,

 tgt.block_3)

values

( src.month_year,

  src.total_block_1,

  src.total_block_2,

  src.total_block_3);

Commit;

Select * from source_daily;

DAY_DATE     BLOCK_1    BLOCK_2    BLOCK_3

---------            ----------      ----------      ----------

12-SEP-10           10                 20                 30

13-SEP-10           15                25                  35

14-SEP-10           20                30                  40

01-OCT-10         10                20                  30

Select * from target_monthly;

MONTH_YEAR    BLOCK_1    BLOCK_2    BLOCK_3

----------               ----------       ----------       ----------

     92010                 45                   75                    105

    102010               10                   20                     30