This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Oct 24, 2012 5:41 AM by Jonathan Lewis RSS

Update Failed for Sum of previous row and current row

936666 Newbie
Currently Being Moderated
Hi i need to update the column length of the previous row and current row so i followed this method but i'm unable to update what is problem in my syntax

SQL> begin
2 DECLARE Total number = 0;
3 UPDATE StringOutput set Total = SumOfLength = Total + ColLength;
4 end;
5 /
DECLARE Total number = 0;
*
ERROR at line 2:
ORA-06550: line 2, column 22:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ; not null range default character

if i update without the variable total then my command is succeeded

UPDATE StringOutput set SumOfLength = ColLength;

but i need the previous row+current row count in SumOfLength

Thanks!
  • 1. Re: Update Failed for Sum of previous row and current row
    John Stegeman Oracle ACE
    Currently Being Moderated
    totalNumber :=0;

    := is the PL/SQL assignment operator
  • 2. Re: Update Failed for Sum of previous row and current row
    936666 Newbie
    Currently Being Moderated
    Getting this error now
    SQL> begin
    2 DECLARE Total number := 0;
    3 UPDATE StringOutput set Total = SumOfLength = Total + ColLength;
    4 end;
    5 /
    UPDATE StringOutput set Total = SumOfLength = Total + ColLength;
    *
    ERROR at line 3:
    ORA-06550: line 3, column 1:
    PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following:
    begin function pragma procedure subtype type <an identifier>
    <a double-quoted delimited-identifier> current cursor delete
    exists prior
    The symbol "begin" was substituted for "UPDATE" to continue.
    ORA-06550: line 3, column 46:
    PLS-00103: Encountered the symbol "=" when expecting one of the following:
    . ( , * @ % & - + ; / at mod remainder rem return returning
    <an exponent (**)> where || multiset
    The symbol ". was inserted before "=" to continue.
    ORA-06550: line 4, column 4:
    PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
    following:
    ( begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with
    <an identifier> <a double-quoted
  • 3. Re: Update Failed for Sum of previous row and current row
    John Stegeman Oracle ACE
    Currently Being Moderated
    read the docs about PL/SQL syntax. In general, you should be like this:

    declare

    begin

    end
  • 4. Re: Update Failed for Sum of previous row and current row
    936666 Newbie
    Currently Being Moderated
    i need the previous row+current row count in SumOfLength

    SQL> DECLARE Total number := 0;
    2 begin
    3 UPDATE StringOutput set Total = SumOfLength = Total + ColLength;
    4 end;
    5 /
    UPDATE StringOutput set Total = SumOfLength = Total + ColLength;
    *
    ERROR at line 3:
    ORA-06550: line 3, column 46:
    PL/SQL: ORA-00933: SQL command not properly ended
    ORA-06550: line 3, column 1:
    PL/SQL: SQL Statement ignored

    Thanks!
  • 5. Re: Update Failed for Sum of previous row and current row
    John Stegeman Oracle ACE
    Currently Being Moderated
    You cannot debug? Why 2 equal signs in your update statement?
  • 6. Re: Update Failed for Sum of previous row and current row
    936666 Newbie
    Currently Being Moderated
    As i said i need the previous row+current row count in SumOfLength so i am assigning to a variable Total and then that total will update the next column and so on

    StringOutput table contains SumOfLength, ColLength, and rid

    UPDATE StringOutput set SumOfLength = ColLength; success

    UPDATE StringOutput set Total = SumOfLength = Total + ColLength; fails with declaration .

    Thanks!
  • 7. Re: Update Failed for Sum of previous row and current row
    John Stegeman Oracle ACE
    Currently Being Moderated
    You need to learn about SQL. There is no such thing as the "previous row" in SQL, especially not in an update statement.
  • 8. Re: Update Failed for Sum of previous row and current row
    936666 Newbie
    Currently Being Moderated
    It is possible in SQL Server

    in my SQL Server i have done as

    DECLARE @Total INT = 0
    UPDATE StringOutput set @Total = SumOfLength = @Total + ColLength
    OPTION (MAXDOP 1)

    i followed this link
    http://www.sqlservercentral.com/articles/T-SQL/68467/

    --===== "Quirky Update" shows us the order that an UPDATE uses.
    DECLARE @Counter INT

    SELECT @Counter = 0

    UPDATE TempDB.dbo.TransactionDetail
    SET @Counter = AccountRunningCount = @Counter + 1
    FROM TempDB.dbo.TransactionDetail WITH (TABLOCKX)

    That's why i am looking whether Oracle is having this kind of stuff

    Thanks!
  • 9. Re: Update Failed for Sum of previous row and current row
    John Stegeman Oracle ACE
    Currently Being Moderated
    That's T-SQL, not SQL

    And UPDATE, according to the SQL standard doesn't have an order. In fact, nothing in SQL has an order unless you use an ORDER BY.

    So, perhaps if you can explain in detail what you are trying to do (give us the SQL to create and populate the table and explain with a proper specification what you want to happen), then we can help.
  • 10. Re: Update Failed for Sum of previous row and current row
    936666 Newbie
    Currently Being Moderated
    i need the previous row+current row count in SumOfLength for some calculation

    SQL> UPDATE b set b.SumOfLength = (Select SUM(ColLength) from StringOutput as a where a.RowNum<b.R
    owNum+1) from StringOutput as b;

    UPDATE b set b.SumOfLength = (Select SUM(ColLength) from StringOutput as a where a.RowNum<b.RowNum
    +1) from StringOutput as b
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis
  • 11. Re: Update Failed for Sum of previous row and current row
    John Stegeman Oracle ACE
    Currently Being Moderated
    Sorry, you've failed again.

    No SQL to create a sample table or populate it with data
    No explanation of what "previous row" and "current row" means

    You cannot use rownum like you are. If you'd like some help (it's fairly clear that you need some :) ), please provide the data requested. Your question is also much better suited to the {forum:id=75} forum, but they would request the same things in that forum.
  • 12. Re: Update Failed for Sum of previous row and current row
    936666 Newbie
    Currently Being Moderated
    ColLength SumOfLength
    1     1
    1     2 (1 previous ColLength +1 current ColLength )
    1     3 (1+1 2 previous ColLength +1 current ColLength )
    1     4 (1+1+1 3 previous ColLength +1 current ColLength )
    1     5 (1+1+1+1 4 previous ColLength +1 current ColLength )
    1     6 (1+1+1+1+1 5 previous ColLength +1 current ColLength )
    1     7 (1+1+1+1+1+1 6 previous ColLength +1 current ColLength )
    5     2 (1+1+1+1+1+1+1 7 previous ColLength +5 current ColLength )

    I need sum of SumOfLength
    You cannot use rownum like you are.
    then say i am having a identity column with the same update shall use that query
    instead of

    UPDATE b set b.SumOfLength = (Select SUM(ColLength) from StringOutput as a where a.RowNum<b.RowNum+1) from StringOutput as b;

    can i use this
    UPDATE b set b.SumOfLength = (Select SUM(ColLength) from StringOutput as a where a.Rid<b.Rid+1) from StringOutput as b;

    Rid with identity;

    Thanks
  • 13. Re: Update Failed for Sum of previous row and current row
    John Stegeman Oracle ACE
    Currently Being Moderated
    Still failing...

    SQL TO CREATE THE TABLE
    SQL TO POPULATE IT

    DEFINE "PREVIOUS COLUMN" - when you write things down, there's an obvious "previous column," but not in SQL. You have to be specific in defining it. When you describe it here, don't use SQL Server terminology such as "identity column" - Oracle doesn't have those.

    BE EXPLICIT
    TAKE YOUR TIME when writing the question
  • 14. Re: Update Failed for Sum of previous row and current row
    936666 Newbie
    Currently Being Moderated
    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
1 2 Previous Next

Legend

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