Oracle 用连续数字更新有序列表值

我需要将有序列表重置为连续数字 (ints)，同时保持原始顺序。2,4，6需要变成1,2，3，和6，53，5498一样。我尝试使用rownum:

update T1 
    set SIBLING_ORDER = rownum 
where PARENT_ID_NUM = 1;
复制

但它并不一致地工作 (请参阅LiveSQL，语句5中的更新是正确的，但语句9中的更新不是正确的)，并且:

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

不是合法的语法。

如何在不求助于一些迭代pl/sql的情况下完成更新的排序？

Thanx，D

这是一个简化的测试用例，因此我们可以看到新旧的同级值

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.
复制

我希望内联更新可能会起作用，但是可惜没有

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
复制

我们可以用老式的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.
复制

但是我更喜欢合并，因为您已经在livesql测试用例中发现了

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.
复制