Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Using RANK in an update
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. Received on Thu Dec 21 2006 - 16:04:58 CST
![]() |
![]() |