Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509

    Unanswered: Malformed update string.

    I am using 8.1.7. I could do this in a cursor loop, but I would like to do it in one statement. Any ideas on what is wrong?

    When I issue the following statement:

    UPDATE crbal_allCusts a
    SET a.balance = a.balance - (SELECT SUM(nvl(b.PT_total,0))
    FROM crbal_AntIvc b
    where b.cust_cd = a.cust_cd);


    I am getting the following error

    SET a.balance = a.balance - (SELECT SUM(nvl(b.PT_total,0))
    *
    ERROR at line 350:
    ORA-06550: line 350, column 39:
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
    ( - + mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string>
    ORA-06550: line 353, column 4:
    PLS-00103: Encountered the symbol "UPDATE"
    ============================================
    I think I answered my own question.
    ============================================
    UPDATE crbal_allCusts a
    SET a.balance = (SELECT a.balance - SUM(nvl(b.PT_total,0))
    FROM crbal_AntIvc b
    where b.cust_cd = a.cust_cd);
    Last edited by beilstwh; 07-30-04 at 15:05.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    the pl/sql engine doesn't catch up to the rdbms engine until v9.something, so things don't always work within a begin/end block. you need to move the a.balance after equal sign into the subselect

    UPDATE crbal_allCusts a
    SET a.balance = (SELECT a.balance - SUM(nvl(b.PT_total,0))
    FROM crbal_AntIvc b
    where b.cust_cd = a.cust_cd);
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Since you are doing this inside PL/SQL, try using execute immediate:

    Code:
    ...
    execute immediate 'UPDATE crbal_allCusts a
    SET a.balance = a.balance - (SELECT SUM(nvl(b.PT_total,0))
    FROM crbal_AntIvc b
    where b.cust_cd = a.cust_cd)';
    ...

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    that's just stupid - why use execute immediate for something that doesn't need it? Just move some text around to make the update pl/sql compliant, and still oeprate exactly the same.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by shoblock
    that's just stupid - why use execute immediate for something that doesn't need it? Just move some text around to make the update pl/sql compliant, and still oeprate exactly the same.
    I just realized that it could be done like you posted after I saw your post actually. Excuse my stupidity.

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    if you excuse my arrogance
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    And thank for the answers.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •