0

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
  • Could you please provide the data as DML (INSERT INTO training VALUES (...))? Help us to help you! – Vérace Jun 7 at 15:16
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
| improve this answer | |
  • Nice (+1)! I was going to post the same thing which I got from my pal Mr. Google! Spent ages (well, 1hr 45mins) messing around with different constructs without success :-( ! However, I have a question - surely there's a way of doing a simple UPDATE for what is (or should be) a relatively simple query? – Vérace Jun 7 at 20:20
  • @Balazs Papp Great ! +1 for answer. Please upvote if you find the question insightful. – Mohammad Javed Jun 8 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.