Update a Column with sequential number based on the order by of a column in Oracle Sql I have table with below create scripts


create table training(  
 action_id       NUMBER not null,
 training_id       NUMBER,
 course_name       VARCHAR2(100),
 display_order     NUMBER default 0   


)


  Below is the data


ACTION_ID   TRAINING_ID DISPLAY_ORDER
 675150      770650       0
 675150      780700       0
 675150      780701       0
 675180      770550       0
 675180      780800       0
 675180      780801       0



  I need write a db script that will update  DISPLAY_ORDER so that it
  gets order from 1 order by  TRAINING_ID   for  a particular   ACTION_ID   
  
  So outcome will be


ACTION_ID   TRAINING_ID DISPLAY_ORDER
 675150      770650       1
 675150      780700       2
 675150      780701       3
 675180      770550       1
 675180      780800       2
 675180      780801       3
create table training(  
 action_id       NUMBER not null,
 training_id       NUMBER,
 course_name       VARCHAR2(100),
 display_order     NUMBER default 0 
);

insert into training (action_id, training_id) values (675150, 770650);
insert into training (action_id, training_id) values (675150, 780700);
insert into training (action_id, training_id) values (675150, 780701);
insert into training (action_id, training_id) values (675180, 770550);
insert into training (action_id, training_id) values (675180, 780800);
insert into training (action_id, training_id) values (675180, 780801);
commit;

select action_id, training_id, display_order from training;

 ACTION_ID TRAINING_ID DISPLAY_ORDER
---------- ----------- -------------
    675150      770650             0
    675150      780700             0
    675150      780701             0
    675180      770550             0
    675180      780800             0
    675180      780801             0


Then with some magic:

merge into training t using
(select action_id, training_id, row_number() over (partition by action_id order by training_id)
  as display_order
from training) t2
on (t.action_id = t2.action_id and t.training_id = t2.training_id)
when matched then update set t.display_order = t2.display_order;
commit;

select action_id, training_id, display_order from training;

 ACTION_ID TRAINING_ID DISPLAY_ORDER
---------- ----------- -------------
    675150      770650             1
    675150      780700             2
    675150      780701             3
    675180      770550             1
    675180      780800             2
    675180      780801             3