DELETE, and the duplicate rows everyone gets one day

DELETE FROM t WHERE ... removes the rows that match. The interesting version is the one every data person meets eventually: a load ran twice, and now some rows are in the table two times.

The test case

The ball-by-ball for match 1, first innings, was loaded, and then overs 5 to 8 were loaded again by mistake. Remove the duplicates, keeping one copy of each ball.

Setup

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

Setup

create table d94_balls_a as
select match_id, innings_no, seq_no, over_no, ball_no, batter_id, runs_batter
from   cr_deliveries
where  match_id = 1 and innings_no = 1;

Setup

insert into d94_balls_a
select match_id, innings_no, seq_no, over_no, ball_no, batter_id, runs_batter
from   cr_deliveries
where  match_id = 1 and innings_no = 1 and over_no between 5 and 8;

Setup

create table d94_balls_b as select * from d94_balls_a;

Setup

create table d94_balls_c as select * from d94_balls_a;

The duplicates are identical in every column, so there's no column that tells the copies apart. That's what ROWID is for: every row's physical address, unique even when the data isn't.

Expected result

Back to 128 balls, one row each (the faulty load had added 24 more), and the runs add up again.

Way 1: keep the lowest ROWID per ball

Setup

delete from d94_balls_a
where  rowid not in (select min(rowid)
                     from   d94_balls_a
                     group  by match_id, innings_no, seq_no);

select count(*) as balls, count(distinct seq_no) as distinct_balls, sum(runs_batter) as runs
from   d94_balls_a;

For each ball (the key match_id, innings_no, seq_no), keep the row with the smallest ROWID and delete the rest. Short, and the version you'll find on every forum.

Way 2: ROW_NUMBER

Setup

delete from d94_balls_b
where  rowid in (select rid
                 from   (select rowid as rid,
                                row_number() over (partition by match_id, innings_no, seq_no order by rowid) as rn
                         from   d94_balls_b)
                 where  rn > 1);

select count(*) as balls, count(distinct seq_no) as distinct_balls, sum(runs_batter) as runs
from   d94_balls_b;

Number the copies of each ball 1, 2, ... and delete everything after 1. The ORDER BY decides which copy survives: order by a load timestamp instead of rowid to keep the newest.

Way 3: delete a row if an "earlier" twin exists

Setup

delete from d94_balls_c d
where  exists (select 1
               from   d94_balls_c x
               where  x.match_id = d.match_id and x.innings_no = d.innings_no and x.seq_no = d.seq_no
               and    x.rowid < d.rowid);

select count(*) as balls, count(distinct seq_no) as distinct_balls, sum(runs_batter) as runs
from   d94_balls_c;

"Delete me if there's an identical ball with a smaller address." The first copy has no smaller twin, so it survives.

Before you delete: look

Always run the same condition as a SELECT first:

select match_id, innings_no, seq_no, count(*) from d94_balls_a
group  by match_id, innings_no, seq_no having count(*) > 1;

And afterwards, stop it happening again: a primary key on (match_id, innings_no, seq_no) would have rejected the second load outright (Part 10).

DELETE vs TRUNCATE

DELETE FROM t (no WHERE) removes every row, one at a time, logged, and can be rolled back. TRUNCATE TABLE t empties the table at once, can't be rolled back, and resets its storage. For "empty the staging table before the nightly load", TRUNCATE is the one.

19c and 26ai

The same on both.

Gotchas

• ROWID isn't forever. It can change if a table is moved or rebuilt. Use it within a statement or transaction, never store it.
• NOT IN with a subquery that returns null matches nothing. MIN(ROWID) is never null, so Way 1 is safe; with other columns, prefer NOT EXISTS.