Regarding some update SQL with a running sequence

From: saurangshu <saurangshu_at_gmail.com>
Date: 29 Dec 2005 00:41:44 -0800
Message-ID: <1135845703.955684.249870@g14g2000cwa.googlegroups.com>

Hi SQL Gurus,

I have a simple looking update criterion where I need to update a column of a table depending on its rownum given a particular order by clause.

For example - I need to update the sal column of emp table and want to add a bonus to the sal depending on the rank in the number of days of service. And, the problem is that I have to first order by the emp records using "hire_date asc" and then add the rownum amount of bonus to the current salary column so that the more senior guys get more bonus depending on the rownum in the ordered entries.

So, I tried to create a view like -
select empno, sal, row_number() over (order by hiredate ) num from scott.emp
WHERE deptno = 10

Which returns a result like -

EMPNO    SAL      NUM
7782         2450         1
7839         5000         2
7934         1300         3

...
...

Now, I wanted to update the sal as sal = sal+num and I tried a sql (and different version of it )like -

UPDATE (SELECT (sal+num) inc , sal

              FROM (
                      select empno, sal,
                                row_number() over (order by hiredate )
num
                      from scott.emp
                      where deptno =   10
                      )
               )

SET sal = inc;

It gave me an "ORA-01733: virtual column not allowed here" error.

Could anybody please suggest some workaround SQL for this problem? I will be having around 50000 rows of emp for the deptno 10. I thought of moving the whole logic to PL/SQL based bulk collect approach but I wanted to make sure that this kind of update is not possible in a single SQL statement as it "looks" very much feasible.

Thanks in advance. Received on Thu Dec 29 2005 - 02:41:44 CST