Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Unanswered: DB2 SQL Update error -407

    When attempting to update one table from another I keep getting a SQLCODE -407: "An error occurred while running the query. An UPDATE, INSERT, or SET value is null, but the object column cannot contain null values. (SQL code = -407, SQL state = 23502)

    This is the code I am attempting to update:
    UPDATE TABLE1 T1
    SET T1.FIELDA = (SELECT T2.FIELDA FROM TABLE2 T2
    WHERE T1.RECORD_DATE = T2.RECORD_DATE
    AND T1.RECORD_TIME = T2.RECORD_TIME
    AND T1.RECORD_SEQ = T2.RECORD_SEQ
    AND T1.RECORD_ID = T2.RECORD_ID
    AND T1.F1 = ' ' AND T2.F1 > ' ')

    Why doesn't this simply update FIELDA on T1 with the value of FIELDA from T2 without giving me an error?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess is that there isn't any row being returned by the subquery.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    Posts
    67
    Check if T2.FIELDA is null.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Add "WHERE EXISTS (...)" clause,
    or use MERGE staement instead of UPDATE statement.

  5. #5
    Join Date
    Apr 2014
    Posts
    2

    MERGE Statement SQLCODE -104

    I tried to use the MERGE statement and go another error:

    MERGE INTO TABLE1 AS T1
    USING MAC2.TABLE2 AS T2
    ON (T1.RECORD_ID = T2.RECORD_ID
    AND T1.RECORD_DATE = T2.RECORD_DATE
    AND T1.RECORD_TIME = T2.RECORD_TIME
    AND T1.RECORD_SEQ = T2.RECORD_SEQ)
    WHEN MATCHED THEN UPDATE SET T1.FIELDA = T2.FIELDA;

    "Illegal symbol "TABLE2". Some symbols that might be legal are: (.
    (SQL code = -104, SQL state = 42601)"

    Do I have the syntax wrong? Should I be using "VALUES"?

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
  •