Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Using RANK in an update

Re: Using RANK in an update

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 22 Dec 2006 10:24:33 +0100
Message-ID: <emg88i$705$00$1@news.t-online.com>


Michel Cadot schrieb:

> "Maxim Demenko" <mdemenko_at_gmail.com> a écrit dans le message de news: emf482$4eu$00$1_at_news.t-online.com...

> | Mike C schrieb:
> | > I was trying to fill in blanks in a number field:
> | >
> | >
> | > I am curious though, if there is a way to make the update directly
> | > using RANK?
> |
> |
> | scott_at_ORA102> select * from test;
> |
> | A B
> | ---------- ----------
> | 1 1
> | 2 2
> | 3 4
> | 4 6
> |
> | scott_at_ORA102> update test
> | 2 set b=
> | 3 (with t as ( select a,b,rank() over(order by a) rank_a from test
> | test)
> | 4 select rank_a from t where t.a=test.a and t.b=test.b);
> |
> | 4 rows updated.
> |
> | scott_at_ORA102>
> | scott_at_ORA102> select * from test;
> |
> | A B
> | ---------- ----------
> | 1 1
> | 2 2
> | 3 3
> | 4 4
> |
> | For this approach however, combination of a and b should be unique (it
> | may be not unique, if you go the CTAS way).
> |
> | Best regards
> |
> | Maxim
> 
> Use rowid to identify your rows:
> 
> update test
> set b =
>   (with t as (select rowid rid,rank() over(order by a) rank_a from test)
>    select rank_a from t where t.rid = test.rowid);
> 
> Regards
> Michel Cadot 
> 
> 

Nice hint, thanks!

Best regards

Maxim Received on Fri Dec 22 2006 - 03:24:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US