Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Using RANK in an update

Using RANK in an update

From: Mike C <michaeljc70_at_hotmail.com>
Date: 21 Dec 2006 14:04:58 -0800
Message-ID: <1166738698.137411.216760@80g2000cwy.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US