You Asked

I need to reset an ordered list to be consecutive numbers (ints) while maintaining the original ordering. 2,4,6 needs to become 1,2,3, as does -6, 53, 5498. I tried using rownum:

update T1 
    set SIBLING_ORDER = rownum 
where PARENT_ID_NUM = 1;

but it does not consistently work (see the LiveSQL, the update in statement 5 is correct, but the update in statement 9 is not), and:

update T1  
    set SIBLING_ORDER = row_number() over (partition by PARENT_ID_NUM order by SIBLING_ORDER) 
where PARENT_ID_NUM = 1;

is not legal syntax.

How might ordering the update be accomplished without resorting to some iterative pl/sql?

Thanx, D

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/h8gt73g517govd5t9dec38lp5

and Connor said...

Here's a simplified test case so we can see the old and new sibling values

SQL> create table t ( pid int, oldsib int , newsib int);

Table created.

SQL>
SQL> insert into t values ( 10, 12, null );

1 row created.

SQL> insert into t values  ( 10, 22, null );

1 row created.

SQL> insert into t values  ( 10, 5, null );

1 row created.

SQL> insert into t values  ( 10, -1, null );

1 row created.

SQL>
SQL> insert into t values  ( 20, 11, null );

1 row created.

SQL> insert into t values  ( 20, 6, null );

1 row created.

SQL> insert into t values  ( 20, 111, null );

1 row created.

SQL> insert into t values  ( 20, 0, null );

1 row created.

SQL>
SQL> alter table t add primary key ( pid, oldsib );

Table altered.

I was hoping an inline update might work but alas no

SQL> select t.*, row_number() over ( partition by pid order by oldsib ) as calc_newsib
  2  from t;

       PID     OLDSIB     NEWSIB CALC_NEWSIB
---------- ---------- ---------- -----------
        10         -1                      1
        10          5                      2
        10         12                      3
        10         22                      4
        20          0                      1
        20          6                      2
        20         11                      3
        20        111                      4

8 rows selected.

SQL>
SQL> update
  2  (
  3  select pid, oldsib, newsib, row_number() over ( partition by pid order by oldsib ) as calc_newsib
  4  from t
  5  )
  6  set newsib = calc_newsib;
(
*
ERROR at line 2:
ORA-01732: data manipulation operation not legal on this view

We could do it with good old fashioned SQL

SQL> update t t1
  2  set  newsib =
  3          ( select count(*)
  4            from   t
  5            where  pid = t1.pid
  6            and    oldsib <= t1.oldsib );

8 rows updated.

SQL>
SQL> select * from t order by 1,2;

       PID     OLDSIB     NEWSIB
---------- ---------- ----------
        10         -1          1
        10          5          2
        10         12          3
        10         22          4
        20          0          1
        20          6          2
        20         11          3
        20        111          4

8 rows selected.

but I prefer the MERGE as you've already discovered in your livesql test case

SQL> merge into t
  2  using (
  3    select pid, oldsib, newsib, row_number() over ( partition by pid order by oldsib ) as calc_newsib
  4    from t
  5  ) m
  6  on ( t.pid = m.pid and t.oldsib = m.oldsib )
  7  when matched then
  8   update set newsib = calc_newsib;

8 rows merged.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

really needed?

Racer I., April 12, 2019 - 6:32 am UTC

Hi,

A tricky problem elegantly (within Oracles constraints) solved. However I would question the business case of materializing this. If it got ragged once it may happen again. Do you intent to do this regularly?
Why not just ROW_NUMBER()ing on each select that needs the clean numbers for something (if they are really needed)?
Gap free sequences are a myth.

regards,

Don Simpson, April 12, 2019 - 4:36 pm UTC

Ah yes, the old triangular join. I avoid them like the plague.

After thinking about it some more I came up with:

    update T1 t 
        set t.SIBLING_ORDER = (
            select t2.SIBLING_ORDER 
            from (
                select ID_NUM, 
                    row_number() over (partition by PARENT_ID_NUM order by SIBLING_ORDER, ID_NUM) as SIBLING_ORDER 
                from T1 
                ) t2 
                where t2.ID_NUM = t.ID_NUM 
            )  
        where t.PARENT_ID_NUM = 1;

In response to Racer, I couldn't agree more. The data is purely ordinal and magnitude is immaterial (and never displayed to end users). Unfortunately, it makes the director happy to have gap free sequences. At least it's a utility for a (presumably) one time update. We've addressed the code to prevent the condition at the source.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

really needed?

More to Explore

PL/SQL demos

PL/SQL docs

Update an ordered list value with consecutive numbers

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

really needed?

More to Explore

PL/SQL demos

PL/SQL docs