Re: Using RANK in an update

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 Received on Thu Dec 21 2006 - 17:09:53 CST