Updating existing data with the Rank May 2, 2007Posted by Duncan in Oracle, PeopleSoft, SQL.
In the post ‘Incrementing Counts in SQL’ I detailed a way of ranking rows within key values. This works fine in a select statement, so I was able to use that to insert data into an empty record.
The problem I faced today however, was that the data already existed in a table. I needed to update an existing column with the rank that each row had within the table (there had been multiple inserts into the table, and the rank needed recalculating).
I played around with the problem for a bit and couldn’t see a way of doing it (without a kludge like copying it out to another table and using the rank statement instead of selecting the column). Then it occurred to me that it could be achieved using the two step approach shown here. I wrote a quick App Engine to do it (a DoSelect with a SQL step inside) and it worked a treat. It took 4 minutes to run, and a smart colleague of mine with PL/SQL knowledge bettered me with this (which ran in 15 seconds):
DECLARE CURSOR c1 IS
, RANK () OVER (PARTITION BY x.keyname, x.key1 ORDER BY x.keyname, x.key1, x.key9, x.key2) rk
FROM recname x;
BEGIN FOR c1_rec IN c1 LOOP
UPDATE recname y
SET y.key3 = c1_rec.rk
WHERE y.keyname = c1_rec.keyname
AND y.key1 = c1_rec.key1
AND y.key2 = c1_rec.key2
AND y.key9 = c1_rec.key9;
The above code snippet includes the %Execute(/) command needed for running PL/SQL within an App Engine SQL Step.