Thanks a lot David. This works. On Wed, Aug 31, 2011 at 8:31 AM, ddf <[email protected]> wrote:
> > > 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 > -- 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
