This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Oct 24, 2012 5:41 AM by Jonathan Lewis Go to original post RSS
  • 16. Re: Update Failed for Sum of previous row and current row
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    933663 wrote:
    I am able to update if i'm having a column with identity values i knew that we dont have identity in oracle we will be creating CREATE SEQUENCE id_seq; to increment in oracle as i had identical values i am able to update query

    UPDATE b set b.SumOfLength = (Select SUM(ColLength) from StringOutput as a where a.Rid<b.Rid+1) from StringOutput as b;
    here rid is having unique value as running number so i am able to update
    You need to stop thinking in terms of a language supplied by Microsoft and think either in terms of SQL, or of a procedural language driving an SQL cursor.

    In the first place you need to be able to define a deterministic order for the rows you are interested in; then you need to decide whether you want to do a set update or a row by row update to those rows. For example, assuming you had the following:
    SQL> desc t1
     Name                          Null?    Type
     ----------------------------- -------- ---------
     ID                            NOT NULL NUMBER
     N1                                     NUMBER
     RUNNING_TOT                            NUMBER(9)
    Assume that the ID column was defined as a unique or primary key, so that it allows you to define a fixed ordering of rows.
    Consider the following select statement:
    select
         id,
         n1,
         running_tot,
         sum(n1) over (order by id)     tot
    from
         t1
    The TOT column will be a running total of the N1 column over the ID column. So it would be nice to be able to write the folloiwng update:
    update
         (
         select
              id,
              n1,
              running_tot,
              sum(n1) over (order by id)     tot
         from
              t1
         )
    set
         running_tot = tot
    ;
    Unfortunately the inline view is not accepted as an updatable view in Oracle, so you can't do this.
    However, you could use the view to update the original table:
    merge
         into t1     od
    using     (
         select
              id,
              n1,
              running_tot,
              sum(n1) over (order by id)     tot
         from
              t1
         )     nd
    on
         (nd.id = od.id)
    when matched then
         update set od.running_tot = nd.tot
    ;
    Or you could wrap a little bit of PL/SQL around the query and update row by row which (under the covers) is probably what your SQL Server example is doing:
    declare
         cursor c1 is
              select
                   id,
                   n1,
                   running_tot,
                   sum(n1) over (order by id)     tot
              from
                   t1
              for update of 
                   running_tot
         ;
    begin
         for r1 in c1 loop
              update t1 set running_tot = r1.tot where current of c1;
         end loop;
         commit;
    end;
    /
    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points