Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013
    Posts
    2

    Unanswered: Update of multiple columns using subselct

    I am trying to update the values in table1 with values from table2. Table2 is a clone of table1. I am using the following update statement that returns a negative 206 on one of the key values:

    update STUDENT.S01207 ATN
    SET (
    ATN.ATTENDANCE_CODE ,
    ATN.HOMEROOM ,
    ATN.STU_LAST_NAME ,
    ATN.STU_FIRST_NAME ,
    ATN.STU_MIDDLE_NAME ,
    ATN.STU_NAME_APPEND ,
    ATN.TERM ,
    ATN.ACTIVITY_DATE ,
    ATN.ACTIVITY_TIME ,
    ATN.USER_NAME ,
    ATN.ATTN_CL_SEQ_NUM ,
    ATN.CD_PRIOR_ATOSS ,
    ATN.ATOSS_SITE ,
    ATN.INCIDENT_NUMBER )
    =
    (SELECT
    ATNC.ATTENDANCE_CODE ,
    ATNC.HOMEROOM ,
    ATNC.STU_LAST_NAME , ATNC.STU_MIDDLE_NAME ,
    ATNC.STU_NAME_APPEND ,
    ATNC.TERM ,
    ATNC.ACTIVITY_DATE ,
    ATNC.ACTIVITY_TIME ,
    ATNC.USER_NAME ,
    ATNC.ATTN_CL_SEQ_NUM ,
    ATNC.CD_PRIOR_ATOSS ,
    ATNC.ATOSS_SITE ,
    ATNC.INCIDENT_NUMBER
    from
    STUDENT.S01207_CHNGS ATNC )
    WHERE
    ATN.DISTRICT_NUMBER = ATNC.DISTRICT_NUMBER
    AND ATN.SCHOOL_YEAR_1 = ATNC.SCHOOL_YEAR_1
    AND ATN.SCHOOL_YEAR_2 = ATNC.SCHOOL_YEAR_2
    AND ATN.SCHOOL_TYPE = ATNC.SCHOOL_TYPE
    AND ATN.SCHOOL_NUMBER = ATNC.SCHOOL_NUMBER
    AND ATN.STUDENT_NUMBER = ATNC.STUDENT_NUMBER
    AND ATN.ATTENDANCE_DATE = ATNC.ATTENDANCE_DATE;
    SQLCODE = -206, ERROR: ATNC.DISTRICT_NUMBER IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE
    IDENTIFIED IN A FROM CLAUSE, OR IS NOT A COLUMN OF THE TRIGGERING TABLE OF A TRIGGER
    SQLSTATE = 42703 SQLSTATE RETURN CODE

    I am DB2 V-8.1 Zos
    ATNC.STU_FIRST_NAME ,

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You probably want to move the conditions into the subselect.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2013
    Posts
    2
    I added the following to the statement and the results were as expected.
    WHERE EXISTS (SELECT 1 FROM STUDENT.S01207_CHNGS ATND
    WHERE
    ATN.DISTRICT_NUMBER = ATND.DISTRICT_NUMBER
    AND ATN.SCHOOL_YEAR_1 = ATND.SCHOOL_YEAR_1
    AND ATN.SCHOOL_YEAR_2 = ATND.SCHOOL_YEAR_2
    AND ATN.SCHOOL_TYPE = ATND.SCHOOL_TYPE
    AND ATN.SCHOOL_NUMBER = ATND.SCHOOL_NUMBER
    AND ATN.STUDENT_NUMBER = ATND.STUDENT_NUMBER
    AND ATN.ATTENDANCE_DATE = ATND.ATTENDANCE_DATE )

    Thank you for the help

Tags for this Thread

Posting Permissions

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