Re: Sorting update?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 21 Sep 1999 14:26:53 -0400
Message-ID: <Ls3nN7sSI3pFqZkLJfbpE5GzIp9r@4ax.com>

On Tue, 21 Sep 1999 17:55:08 GMT, Thorsten Koch <torsti_engy_at_my-deja.com> wrote:

>Hi folks!
>
>Let there be table EMP with
>POS NUMBER
>NAME VARCHAR2
>SAL NUMBER
>
>and the data
>POS NAME SAL
>----- ------ -----
>1 A 1000
>2 C 500
>3 B 700
>
>Now I want to update the table in a way that its sorted by salaries, so
>that it looks like this:
>
>POS NAME SAL
>----- ------ -----
>1 C 500
>2 B 700
>3 A 1000

how about this...

declare
  l_cnt number := 1;
begin
  for c in ( select rowid r

               from emp 
              order by sal )

  loop
    update emp

       set pos = l_cnt
     where rowid = c.r;

    l_cnt := l_cnt + 1;
  end loop;
end;
/

>
>That is, I would like to have an update-statement like
>
>update emp
>set pos=(select rownum
> from emp
> order by sal);
>
>Unfortunately, it is not possible to use "order by" in an update
>statement.
>
>Can someone advise me how to perform this task? I would be glad to
>receive a few different solutions, because not all solutions may fit to
>my special application.
>
>Many thanks,
>
>Thorsten
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 21 1999 - 13:26:53 CDT