UPDATE: changing rows from another table

Updating a column to a fixed value is easy (update t set status = 'X' where ...). The everyday hard case is setting a column from other data: a total, a latest date, a looked-up name.

The test case

Give every player a career_runs column holding their runs over both seasons, 0 for players who never batted.

Setup

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

Setup

create table d92_players_a as
select player_id, player_name, team_id, cast(null as number) as career_runs from cr_players;

Setup

create table d92_players_b as select * from d92_players_a;

Setup

create table d92_players_c as select * from d92_players_a;

Expected result

Checked team by team:

Six players end both seasons on 0, three of them at Karachi: tail-enders who padded up, watched, and went home. Cricket's loneliest job.

Way 1: a correlated subquery

Setup

update d92_players_a p
set    p.career_runs = nvl((select sum(d.runs_batter)
                            from   cr_deliveries d
                            where  d.batter_id = p.player_id), 0);

select t.team_code, count(*) as players, sum(p.career_runs) as runs,
       max(p.career_runs) as best, count(case when p.career_runs = 0 then 1 end) as on_zero
from   d92_players_a p
join   cr_teams t on t.team_id = p.team_id
group  by t.team_code
order  by t.team_code;

For each player row, the subquery sums that player's runs. No WHERE on the UPDATE, so every row is set; NVL(..., 0) turns "never batted" (null) into 0.

Way 2: MERGE

Setup

merge into d92_players_b p
using (select pl.player_id, nvl(sum(d.runs_batter), 0) as runs
       from   cr_players pl
       left   join cr_deliveries d on d.batter_id = pl.player_id
       group  by pl.player_id) s
on    (s.player_id = p.player_id)
when matched then update set p.career_runs = s.runs;

select t.team_code, count(*) as players, sum(p.career_runs) as runs,
       max(p.career_runs) as best, count(case when p.career_runs = 0 then 1 end) as on_zero
from   d92_players_b p
join   cr_teams t on t.team_id = p.team_id
group  by t.team_code
order  by t.team_code;

Compute all the totals once, then match them to the rows. On big tables this is usually much faster than Way 1, which (logically) runs the subquery once per row.

Way 3: two simple updates

Setup

update d92_players_c set career_runs = 0;

Setup

update d92_players_c p
set    p.career_runs = (select sum(d.runs_batter) from cr_deliveries d where d.batter_id = p.player_id)
where  exists (select 1 from cr_deliveries d where d.batter_id = p.player_id);

select t.team_code, count(*) as players, sum(p.career_runs) as runs,
       max(p.career_runs) as best, count(case when p.career_runs = 0 then 1 end) as on_zero
from   d92_players_c p
join   cr_teams t on t.team_id = p.team_id
group  by t.team_code
order  by t.team_code;

The WHERE EXISTS is the important bit: without it, players who never batted would have their 0 overwritten by the subquery's null. Forgetting it is the classic correlated-update bug.

19c and 26ai

All three run on both. 26ai adds UPDATE ... FROM, a join right in the UPDATE (page 12.6).

Gotchas

• An UPDATE without WHERE changes every row. That's what we wanted here; usually it isn't. Run the WHERE as a SELECT COUNT(*) first.
• Locks: updated rows stay locked until you commit or roll back, and anyone else trying to update them waits. SELECT ... FOR UPDATE takes those locks up front, before you decide what to change; FOR UPDATE SKIP LOCKED lets several workers grab different rows from a queue.
• ORA-01427 in Way 1 means the subquery returned more than one row for some player: it needs an aggregate or a tighter WHERE.