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