Re: Regarding some update SQL with a running sequence

"saurangshu" <saurangshu_at_gmail.com> a écrit dans le message de news: 1135845703.955684.249870_at_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.
|

Maybe:

=?/? SCOTT>update emp a set sal=(select sal+row_number() over (order by hiredate) from emp b where b.empno=a.empno);

14 rows updated.

=?/? SCOTT>select empno, sal, row_number() over (order by hiredate ) num   2 from scott.emp
  3 WHERE deptno = 10
  4 ;

     EMPNO SAL NUM
---------- ---------- ----------

      7782       2451          1
      7839       5001          2
      7934       1301          3

3 rows selected.

Regards
Michel Cadot Received on Thu Dec 29 2005 - 03:57:20 CST