Q:

For every Request_number, assign the v_id on each voucher a number starting with 1 and incrementing by 1

Hi everyone I need some help, please.
I have to do the following with pl/sql:
For every Request_number, assign the v_id on each voucher a number starting with 1 and incrementing by 1.
For example, If Request number 786530 has 3 vouchers associated to it, the first voucher should v_id 1, the second voucher should have v_id 2, the third voucher should have v_id 3.
Table looks like this
Request_NO  Voucher_no  V_id
200 22211   null
200 22212   null
200 22213   null
201 22214   null
202 22215   null
202 22216   null
203 22217   null
204 22218   null

A:

You can use a merge for efficiency:
create table vouchers
(request_no number not null
,voucher_no number primary key
,v_id number);

insert into vouchers values (200,22211,null);
insert into vouchers values (200,22212,null);
insert into vouchers values (200,22213,null);
insert into vouchers values (201,22214,null);
insert into vouchers values (202,22215,null);
insert into vouchers values (202,22216,null);
insert into vouchers values (203,22217,null);
insert into vouchers values (204,22218,null);

merge into vouchers t
using (select voucher_no
             ,row_number()
              over (partition by request_no
                    order by 1)
              as new_v_id
       from vouchers) s
on (t.voucher_no = s.voucher_no)
when matched then update set v_id = new_v_id;

select * from vouchers;

req vouch   v_id
=== =====   ====
200 22211   1
200 22212   2
200 22213   3
201 22214   1
202 22215   1
202 22216   2
203 22217   1
204 22218   1

