Results 1 to 6 of 6

Thread: Code Help

  1. #1
    Join Date
    Oct 2001
    Location
    fairview, tn
    Posts
    8

    Unanswered: Code Help

    I am trying to run a table update and get a ORA-01427 error message when I run the following. Any clues why?

    UPDATE LEMONG.CS_WOL_COMN_T
    SET NETPAID_AM = (SELECT (A.ORIGINALPAID_AM-B.ADJ_TTL_AM) NETPAID_AM
    FROM LEMONG.CS_WOL_COMN_T A,
    LEMONG.WARR_ADJ_T B
    WHERE A.FCLTY_NO = B.SRV_DEALER_NO (+)
    AND A.VIN_PFX_CD = B.VIN_PFX_CD (+)
    AND A.VIN_SER_NO = B.VIN_SER_NO (+)
    AND A.WO_NO = B.WO_NO (+)
    AND A.WOL_NO = B.WOL_NO (+)))

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    single-row subquery returns more than one row.

    >SELECT (A.ORIGINALPAID_AM-B.ADJ_TTL_AM) NETPAID_AM......
    This SELECT returns more than 1 row.

    How can multiple values be assigned to NETPAID_AM, a single variable?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2001
    Location
    fairview, tn
    Posts
    8
    I assmued that only 1 value would be returned because of the joins in the select statement. Obviously the wrong assumption........

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking (+)?

    If you use this: (+) join you get ALL rows from the LEMONG.CS_WOL_COMN_T table.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I assmued that only 1 value would be returned because of the joins in the select statement.
    Why "assume"?
    Just invoke the SELECT as a standalone query & see the results.

    Besides, without a WHERE clause on the UPDATE, every row in this table gets assigned the same value!
    I suspect that is not the intended results.
    Last edited by anacedent; 02-02-09 at 16:34.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by anacedent
    Just invoke the SELECT as a standalone query & see the results.
    Good remark. As the inner SELECT contains the LEMONG.CS_WOL_COMN_T table in FROM clause, it in fact does not depend on updated row at all. So it returns its whole resultset for all rows.

    Maybe you should have a look at some examples of update based on SELECT e.g. in this thread: http://www.dbforums.com/oracle/16299...mn-update.html.

Posting Permissions

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