Re-ordering without procedural code or using a sequence 20188367001

Re-ordering without procedural code or using a sequence

Question from one of my customers:
“Please help me find an update statement that’s able to re-order a primary key after a delete.. and by the way.. you are not allowed to use PL/SQL, triggers or a sequence, because we cannot change anything in the app schema”.
Updating a PK seemed a bit odd, but then I was told about a composite PK over 2 columns, with the second column a number, meant to always start with 0 and ascending per value in the first column, and – most importantly! – without any holes in de numbering.

At first I couldn’t see a way to use pure SQL for this request, and thought of PL/SQL loops and using a temporary table.
But after some time I came up with a solution, using row_number() and rowid.
I think it’s a nice example of being pressed to think in sets instead of row based processing, with the solution being short, fast, and rather elegant.
Try it yourself!

— create test table

create table t1( name varchar2(100), seqnr number(4), note varchar2(30));
alter table t1 add ( constraint t_pk primary key ( name, seqnr));

— insert test data

insert into t1 values ('harry', 0, 'test record 100');
insert into t1 values ('harry', 1, 'test record 101');
insert into t1 values ('harry', 2, 'test record 102');
insert into t1 values ('harry', 3, 'test record 103');
insert into t1 values ('harry', 4, 'test record 104');
insert into t1 values ('ann', 0, 'test record 105');
insert into t1 values ('ann', 1, 'test record 106');
insert into t1 values ('ann', 2, 'test record 107');
insert into t1 values ('bob', 0, 'test record 108');
insert into t1 values ('bob', 1, 'test record 109');
insert into t1 values ('bob', 2, 'test record 110');
insert into t1 values ('bob', 3, 'test record 111');
insert into t1 values ('ronald', 0, 'test record 112');
insert into t1 values ('ronald', 1, 'test record 113');
insert into t1 values ('ronald', 2, 'test record 114');
insert into t1 values ('ronald', 3, 'test record 115');
insert into t1 values ('ronald', 4, 'test record 116');
insert into t1 values ('ronald', 5, 'test record 117');
insert into t1 values ('ronald', 6, 'test record 118');
insert into t1 values ('patrick', 0, 'test record 119');
insert into t1 values ('patrick', 1, 'test record 120');
insert into t1 values ('patrick', 2, 'test record 121');
insert into t1 values ('patrick', 3, 'test record 122');
insert into t1 values ('marie', 0, 'test record 123');
insert into t1 values ('marie', 1, 'test record 124');
commit;

— save the original data in a second table

create table t2 as select * from t1 order by 3;

— delete some rows, creating holes in the sequence for every name

delete t1 where seqnr in (0,2);
commit;

— reset the sequence number, starting with 0 again, filling up any hole in
— the numbering, in fact, updating a part of the composite primary key!

update t1
set seqnr = ( select seqnr_new 
              from ( select rowid rid
                          ,(row_number() over ( partition by name order by seqnr ))-1 seqnr_new 
                     from t1 ) t0
              where t1.rowid = t0.rid );
commit;

— new sequence starts at 0 again, and isn’t showing any holes!

select * from t1;

— start again

drop table t1 cascade constraints purge;
create table t1 as select * from t2 order by 3;
alter table t1 add ( constraint t1_pk primary key ( name, seqnr));

— drop all

drop table t1 cascade constraints purge;
drop table t2 cascade constraints purge;

5 Comments

  1. savy steve November 2, 2012
  2. Stew Ashton October 5, 2012
    • Harry Dragstra October 7, 2012
  3. Harry Dragstra October 4, 2012
  4. Rob van Wijk October 4, 2012