Re: Update based on order

On 25 Nov 2001 07:27:51 -0800, prakashcn_at_hotmail.com (Prakash C N) wrote:

>Hi
>
> can i execute an update statement based on order,
> for example
> update tab1 set col1 = running_number
> where col2 = 1 order by col3 desc
>
> here col1 will be geting a running number that means for
> each record updated col1 will have the next number
>
> sample output before update
> col1 col2 col3
> 5 1 Z
> 6 1 a
> 4 1 T
> 1 2 Y
> 2 2 x
>
> sample output after update
> col1 col2 col3
> 4 1 T
> 5 1 Z
> 6 1 a
> 1 2 Y
> 2 2 x

Pure SQL has no concept of record order, only cursors do. You have to use a PL/SQL procedure for something like this, then you can open a cursor with an ORDER BY clause and use a local variable for the updated value which increments by one in each loop iteration.

Bob Hairgrove
rhairgroveNoSpam_at_Pleasebigfoot.com Received on Sat Dec 01 2001 - 11:55:04 CST