Re: Using RANK in an update

"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 Received on Fri Dec 22 2006 - 00:58:40 CST