在PLSQL Oracle中根據ID和日期向現有行添加序列號

我想SEQNO根據它們添加一個序列號 ( )REFNO和STARTD。

begin
For r in (Select t.*,
 row_number() over (partition by refno order by startd) seqno
 From my_table t
 Order by refno, startd) loop
  Update my_table
    set seqno = r.seqno
    where refno = r.refno
      and startd = r.startd;
End loop;
Commit;
End;

有關 row_number 的範例，請參見http://sqlfiddle.com/#!4/484b3/5。這個 pl/sql 匿名塊只能處理有限數量的記錄。

您可以使用MERGE：

merge 
 into tableX t
 using 
 ( select t.*, 
          row_number() over (partition by refno order by startd) as rn
   from tableX t
 ) r
 on ( t.refno = r.refno and t.startd = r.startd )
when matched
 then update
 set
  seqno = r.rn ;

在**dbfiddle.uk**中進行測試。

（我假設PK是(refno, startd)

引用自：https://dba.stackexchange.com/questions/219322