Re: Regarding some update SQL with a running sequence

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 29 Dec 2005 15:05:26 +0100
Message-ID: <43b3ed25$0$14568$626a14ce@news.free.fr>

"saurangshu" <saurangshu_at_gmail.com> a écrit dans le message de news: 1135860627.183198.131390_at_g14g2000cwa.googlegroups.com...
| Hi Michel,
|
| I think there is a problem here - all the rows are updated by only one
| (i.e. 2450 ->2451, 1300 ->1301 etc). So, it is not adding the num but
| making a shorteer window of size one and the row_number() function is
| returning 1 everytime.
| Any clue to resolve this?
|
| Thanks a lot for the reply.
|

Sorry, writing to fast and forget one level:

=?/? SCOTT>update emp a
  2 set sal=(select sal+rn

  3           from (select empno, sal, row_number() over (order by hiredate) rn from emp where deptno=10) b
  4            where b.empno=a.empno)

  5 where deptno = 10;

3 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       5002          2
      7934       1303          3

3 rows selected.

Regards
Michel Cadot Received on Thu Dec 29 2005 - 08:05:26 CST