MERGE: update if it's there, insert if it isn't

"Upsert" is the bread and butter of data loading: new data arrives, some of it about things you already have (update them) and some about new things (insert them). MERGE does both in one statement, in one pass.

The test case

A batting table holds 2025 totals: player, runs, innings, last season played. Fold the 2026 numbers in: add to the players already there, and add the players new in 2026.

Setup

begin
  for t in (select table_name from user_tables where table_name in ('D93_BAT_A', 'D93_BAT_B')) loop
    execute immediate 'drop table ' || t.table_name || ' purge';
  end loop;
end;
/

Setup

create table d93_bat_a as
select batter_id as player_id, sum(runs_batter) as runs,
       count(distinct match_id) as innings, 2025 as last_season
from   cr_balls_v
where  season = 2025
group  by batter_id;

Setup

create table d93_bat_b as select * from d93_bat_a;

That's 61 players from 2025. In 2026, 65 batted: 60 of them old faces, 5 new.

Expected result

66 players in all: 65 who batted in 2026 and the 1 who didn't come back.

Way 1: MERGE

Setup

merge into d93_bat_a t
using (select batter_id, sum(runs_batter) as runs, count(distinct match_id) as innings
       from   cr_balls_v
       where  season = 2026
       group  by batter_id) s
on    (t.player_id = s.batter_id)
when matched then update
     set t.runs = t.runs + s.runs, t.innings = t.innings + s.innings, t.last_season = 2026
when not matched then insert (player_id, runs, innings, last_season)
     values (s.batter_id, s.runs, s.innings, 2026);

select count(*) as players, sum(runs) as runs, sum(innings) as innings,
       count(case when last_season = 2026 then 1 end) as batted_2026,
       count(case when last_season = 2025 then 1 end) as not_back
from   d93_bat_a;

• ON decides "is this row already there?". Matched rows are updated, the rest inserted.
• You can't update the columns used in ON (ORA-38104).

Way 2: UPDATE, then INSERT what's missing

Setup

update d93_bat_b t
set    (runs, innings, last_season) =
       (select t.runs + sum(b.runs_batter), t.innings + count(distinct b.match_id), 2026
        from   cr_balls_v b
        where  b.season = 2026 and b.batter_id = t.player_id)
where  exists (select 1 from cr_balls_v b where b.season = 2026 and b.batter_id = t.player_id);

Setup

insert into d93_bat_b (player_id, runs, innings, last_season)
select batter_id, sum(runs_batter), count(distinct match_id), 2026
from   cr_balls_v b
where  season = 2026
and    not exists (select 1 from d93_bat_b t where t.player_id = b.batter_id)
group  by batter_id;

select count(*) as players, sum(runs) as runs, sum(innings) as innings,
       count(case when last_season = 2026 then 1 end) as batted_2026,
       count(case when last_season = 2025 then 1 end) as not_back
from   d93_bat_b;

Two statements and two passes, but each is plain SQL. The order matters: insert first and the update would add 2026 runs to the brand-new rows a second time.

The rest of MERGE

merge into target t
using source s
on    (t.id = s.id)
when matched then update set t.val = s.val
     where s.val <> t.val                      -- only touch rows that really changed
     delete where t.val is null                -- then delete updated rows that meet this
when not matched then insert (id, val) values (s.id, s.val)
     where s.val is not null;                  -- insert only some of the new ones

Either WHEN branch can be left out: a MERGE with only WHEN MATCHED is the 19c way to update from a join (page 9.2).

19c and 26ai

The same on both.

Gotchas

• ORA-30926: unable to get a stable set of rows. The source has two rows for the same target row. Aggregate or de-duplicate the source first.
• MERGE isn't magic about concurrency. Two sessions merging the same new key at the same time can both decide "not matched" and both insert; a unique constraint is what stops the duplicate.