Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2005
    Posts
    23

    Unanswered: Update Statement

    Hi everyone!
    Please help!

    1) ACT_VAL_04_TOT tbl to be updated
    Columns:
    SSN, (populated already)
    EARN, (populated already)
    HRS, (populated already)
    Q1_EARN, (needs to be updated)
    Q1_HRS, (needs to be updated)
    Q2_EARN,
    Q2_HRS,
    Q3_EARN,
    Q_3_HRS,
    Q4_EARN,
    Q4_HRS

    Here is the table contains the values to update the
    ACT_VAL_04_QTR with
    SSN
    EARN
    HRS
    QTR
    Where earn = $earing, HRS=$HRS & QTR represents 1,2,3,4 meaning quarters

    Here is my update statement:

    Code:
    update   BASYS.ACT_VAL_04_TOT set (ACT_VAL_04_TOT.Q1_EARN,  ACT_VAL_04_TOT.Q1_HRS)=
    (select  ACT_VAL_04_QTR.EARN,ACT_VAL_04_QTR.HRS
    FROM BASYS.ACT_VAL_04_QTR , BASYS.ACT_VAL_04_TOT
    WHERE ACT_VAL_04_QTR.SSN = ACT_VAL_04_TOT.SSN AND ACT_VAL_04_QTR.QTR = 1);
    [u]..& the error:

    SQL0811N The result of a scalar fullselect, SELECT INTO
    statement, or VALUES INTO statement is more than one row.

    Explanation:

    One of the following caused the error:

    o Execution of an embedded SELECT INTO or VALUES INTO statement
    resulted in a result table of more than one row.

    o Execution of a scalar fullselect resulted in a result table
    of more than one row.



    Federated system users: this situation can be detected by
    federated server or by the data source.

    The statement cannot be processed.

    User Response:

    Ensure that the statement contains the proper condition
    specifications. If it does, there may be a data problem that is
    causing more than one row to be returned when only one is
    expected.

    Federated system users: isolate the problem to the data source
    failing the request (refer to the problem determination guide to
    determine which data source is failing to process the SQL
    statement) and examine the selection criteria and data for that
    object.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You are attempting to update each row of ACT_VAL_04_TOT with all rows from ACT_VAL_04_QTR where QTR = 1.

    Remove the underlined part:



    Code:
    update   BASYS.ACT_VAL_04_TOT 
    set (ACT_VAL_04_TOT.Q1_EARN,  ACT_VAL_04_TOT.Q1_HRS)=
    (select  ACT_VAL_04_QTR.EARN,ACT_VAL_04_QTR.HRS
    FROM BASYS.ACT_VAL_04_QTR , BASYS.ACT_VAL_04_TOT
    WHERE ACT_VAL_04_QTR.SSN = ACT_VAL_04_TOT.SSN AND ACT_VAL_04_QTR.QTR = 1);

  3. #3
    Join Date
    Mar 2005
    Posts
    23
    thank you
    it runs for ever now?!
    Any suggestions?

  4. #4
    Join Date
    Jan 2005
    Posts
    191
    Do both tables have an index on SSN? Are both tables clustered by SSN?

    Other way is to

    SELECT 'UPDATE BASYS.ACT_VAL_04_TOT SET Q1_EARN = ' CONCAT CHAR(EARN) CONCAT ', Q1_HRS = ' CONCAT CHAR(HRS) CONCAT ' WHERE SSN = ' CONCAT SSN CONCAT ' ; ' FROM BASYS.ACT_VAL_04_QTR WHERE QTR=1;

    (adjust as appropriate for actual data types)

    and run resulting update statements.

    I cannot help but wonder if SSN holds a USA Social Security Number. Are you aware that it is non-unique (as in two people can have the same number)?

    James Campbell

  5. #5
    Join Date
    Dec 2004
    Location
    India
    Posts
    23
    Hii..
    I do have the same kind of problem in UPDATE statement in DB2 where I want to update a table from another table and the condition includes both the tables.

    As U said...I tried the query removing the underlined part but still it is giving the error of Full Select. Is there any other way out???

    -Madhuri.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by madhuri_awal
    Hii..
    I do have the same kind of problem in UPDATE statement in DB2 where I want to update a table from another table and the condition includes both the tables.

    As U said...I tried the query removing the underlined part but still it is giving the error of Full Select. Is there any other way out???

    -Madhuri.
    The feature in DB2 that allows that was implemented with a DB2 version 8 fixpak. I think it was fixpak 2 or 4, but not absolutely sure.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    "select ACT_VAL_04_QTR.EARN,ACT_VAL_04_QTR.HRS
    FROM BASYS.ACT_VAL_04_QTR WHERE ACT_VAL_04_QTR.SSN = ACT_VAL_04_TOT.SSN AND ACT_VAL_04_QTR.QTR = 1"

    It is evident that this select query is returning more than a value for each SSN.
    It seems if you write "Select distinct .." than may be it would return a single value.

    Please try this and let us know.

    Jayanta

Posting Permissions

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