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.

One method that does not involve dropping the existing table, creating
a new table, or TRUNCATING the existing table:
What we are attempting to accomplish:
SELECT
A,
B,
RANK() OVER (ORDER BY A,B) RANKING
FROM
TEST;

A B RANKING
1 1 1
2 2 2
3 4 3
4 6 4

Once the RANKING column contains the correct values, we can slide the
values into column B, then drop the RANKING column.

ALTER TABLE
TEST
ADD (
RANKING NUMBER);

Putting the values into the RANKING column is a bit challenging, unless
we use inline views:
UPDATE
TEST T1
SET
RANKING=(
SELECT
RANKING
FROM
(SELECT
A,
B,
RANK() OVER (ORDER BY A,B) RANKING
FROM
TEST) T2
WHERE
T1.A=T2.A
AND T1.B=T2.B);

The inner-most inline view retrieves the RANK, and the values of A and
B. The outer inline view strips out all but the one RANKING value that
is of interest for the row being updated. This is an expensive
operation if the table contains many rows.

SELECT
*
FROM
TEST;

A B RANKING
1 1 1
2 2 2
3 4 3
4 6 4

UPDATE
TEST
SET
B=RANKING;

ALTER TABLE
TEST
DROP COLUMN
RANKING;

SELECT
*
FROM
TEST;

A B
1 1
2 2
3 3
4 4

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

.