Joins in UPDATE and DELETE, and RETURNING OLD and NEW

Changing rows based on another table is everyday work: set a total from a summary, delete the rows that match a list. On 19c that means MERGE, correlated subqueries or IN (...). On 26ai UPDATE and DELETE can join directly with a FROM clause, and RETURNING can hand back both the value before the change and the value after it.

This page changes data, so it works on its own small copies: cr_runs_26 (changed the 26ai way) and cr_runs_19c (changed the 19c way). The tests then check that both copies end up identical.

Step 1: a table to change

Each copy starts as one row per player with 0 runs.

Setup

drop table if exists cr_runs_26;

Setup

create table cr_runs_26 as
select player_id, player_name, team_id, 0 as runs from cr_players;

Setup

begin
  execute immediate 'drop table cr_runs_19c purge';
exception when others then
  if sqlcode != -942 then raise; end if;
end;
/

Setup

create table cr_runs_19c as
select player_id, player_name, team_id, 0 as runs from cr_players;

Step 2: UPDATE from a summary

Set each player's runs to their runs in 2026.

26ai: UPDATE ... FROM

Setup

update cr_runs_26 r
set    r.runs = s.runs
from   (select batter_id, sum(runs_batter) as runs
        from   cr_balls_v
        where  season = 2026
        group  by batter_id) s
where  s.batter_id = r.player_id;

The FROM clause brings in the summary, and the WHERE joins it to the row being updated. Players who didn't bat in 2026 have no row in s, so they aren't touched and keep 0.

19c: MERGE

Setup

merge into cr_runs_19c r
using (select batter_id, sum(runs_batter) as runs
       from   cr_balls_v
       where  season = 2026
       group  by batter_id) s
on    (s.batter_id = r.player_id)
when matched then update set r.runs = s.runs;

MERGE with only WHEN MATCHED is the standard 19c way to update from a join. The other 19c way is a correlated subquery, set runs = (select ... where batter_id = r.player_id), with a WHERE EXISTS so non-batters don't get null.

Step 3: DELETE with a join

Remove every Auckland player.

26ai: DELETE ... FROM

Setup

delete cr_runs_26 r
from   cr_teams t
where  t.team_id = r.team_id
and    t.team_code = 'AKL';

19c: IN (subquery)

Setup

delete from cr_runs_19c
where  team_id in (select team_id from cr_teams where team_code = 'AKL');

Sorry, Auckland. It's only a demo table.

Step 4: RETURNING OLD and NEW

Give Karthik Raman 10 bonus runs, and log his runs before and after.

Setup

drop table if exists cr_runs_log_26;

Setup

create table cr_runs_log_26 (player_name varchar2(40), old_runs number, new_runs number);

26ai: RETURNING OLD ..., NEW ...

Setup

declare
  l_old number;
  l_new number;
begin
  update cr_runs_26
  set    runs = runs + 10
  where  player_name = 'Karthik Raman'
  returning old runs, new runs into l_old, l_new;
  insert into cr_runs_log_26 values ('Karthik Raman', l_old, l_new);
end;
/

One statement changes the row and reports both values. Without OLD/NEW, RETURNING gives the new value.

19c: read first, then update

Setup

begin
  execute immediate 'drop table cr_runs_log_19c purge';
exception when others then
  if sqlcode != -942 then raise; end if;
end;
/

Setup

create table cr_runs_log_19c (player_name varchar2(40), old_runs number, new_runs number);

Setup

declare
  l_old number;
  l_new number;
begin
  select runs into l_old
  from   cr_runs_19c
  where  player_name = 'Karthik Raman'
  for update;                                  -- lock the row so nobody changes it in between
  update cr_runs_19c
  set    runs = runs + 10
  where  player_name = 'Karthik Raman'
  returning runs into l_new;
  insert into cr_runs_log_19c values ('Karthik Raman', l_old, l_new);
end;
/

Two statements and a lock where 26ai needs one statement. The FOR UPDATE matters: without it, another session could change the row between the SELECT and the UPDATE, and the "old" value in your log would be wrong.

Did both ways give the same result?

The top five

26ai only

select player_name, runs from cr_runs_26 order by runs desc, player_name fetch first 5 rows only;

select player_name, runs from cr_runs_19c order by runs desc, player_name fetch first 5 rows only;

What's left after the delete and the bonus

26ai only

select count(*) as players, sum(runs) as runs from cr_runs_26;

select count(*) as players, sum(runs) as runs from cr_runs_19c;

72 players minus Auckland's 12 is 60. The runs are everyone else's 2026 runs plus Karthik's 10.

The log

26ai only

select player_name, old_runs, new_runs from cr_runs_log_26;

select player_name, old_runs, new_runs from cr_runs_log_19c;

19c and 26ai

Gotchas

• One target row, several matches. If the FROM source has two rows for the same target row, the update is ambiguous and Oracle stops with ORA-30926: The operation attempted to update the same row twice (the same error MERGE gives). Summarise the source first, as above.
• Rows with no match aren't touched by UPDATE ... FROM or MERGE ... WHEN MATCHED. A correlated SET col = (subquery) does touch them, setting null, unless you add WHERE EXISTS.
• Test DML on a copy. Exactly what this page does. Production tables deserve the same courtesy.