On Aug 30, 4:36 pm, Gayathri <[email protected]> wrote:
> Hi All,
>
> Could you please help me with this asap.
>
> I have a table as below:
> (all the value in target_key is null)
>
> source_key target_key
> 20
> 1
> 31
> 8
>
> This is what I need to acheive:
> source_key target_key
> 1 100
> 8 101
> 20 102
> 31 103
>
> Need to update target_key with a series on number after sorting the
> source_key.
>
> I generated the number 100 - 103 using the query below and have collected
> into a collection. *But facing problem to update with order by *
>
> as
> ..
>
> TYPE ntable_key_list_type IS TABLE OF NUMBER;
>
> v_key_list ntable_key_list_type;
> ...
>
> begin
> ..
> ..
>
> *Select Rownum
> bulk collect into v_key_list
> From dual Connect By Rownum <= v_rec_cnt;*
>
> forall i in v_key_list.first .. v_key_list.last
> update
> ....;
>
> end
>
> Please suggest asap. Thanks in advance
> G
You're taking the long way around:
SQL> create table gayathri_stuff(
2 stuff_key number,
3 target_key number
4 );
Table created.
SQL>
SQL> insert all
2 into gayathri_stuff(stuff_key)
3 values(20)
4 into gayathri_stuff(stuff_key)
5 values(1)
6 into gayathri_stuff(stuff_key)
7 values(31)
8 into gayathri_stuff(stuff_key)
9 values(8)
10 select * From dual;
4 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * From gayathri_stuff;
STUFF_KEY TARGET_KEY
---------- ----------
20
1
31
8
SQL>
SQL> declare
2 cursor get_sorted is
3 select stuff_key from gayathri_stuff order by stuff_key;
4
5 tgt_key number:=100;
6
7 begin
8 for rec in get_sorted loop
9 update gayathri_stuff
10 set target_key = tgt_key
11 where stuff_key = rec.stuff_key;
12
13 tgt_key := tgt_key + 1;
14 end loop;
15
16 commit;
17
18 end;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * From gayathri_stuff;
STUFF_KEY TARGET_KEY
---------- ----------
20 102
1 100
31 103
8 101
SQL>
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
