Re: Using RANK in an update

Mike C wrote:
> I was trying to fill in blanks in a number field:
>
> CREATE TABLE test (a number, b number) ;
>
> insert into test values (1,1);
> insert into test values (2,2);
> insert into test values (3,4);
> insert into test values (4,6);
>
> I am looking for 4 to become 3 and 6 to become 4.
>
> When I tried:
>
> UPDATE test t1 set b= (SELECT rank() OVER (ORDER BY a) FROM test t2
> where
> t1.a=t2.a);
> I get 1 for everything for column b. I assume it is processing the
> rank 4 times (0ne at a time) and gets 1 because of the join.
>
> I got around this by doing the following, which works:
>
> CREATE TABLE test2 as
> SELECT a, rank() OVER (ORDER BY a) b FROM test;
>
> TRUNCATE TABLE test;
>
> INSERT INTO test select * from test2;
>
>
> I am curious though, if there is a way to make the update directly
> using RANK?
>
> I tried UPDATE test t1 set b= rank() OVER (ORDER BY a) and that
> doesn't work either.

Just to be clear, I am really trying to fill in gaps, not blanks. Received on Thu Dec 21 2006 - 16:44:07 CST