Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    Unanswered: error in NOT NULL

    Hi all,

    db2 10.1 wse on rhel6, While executing below query :


    update SCRAP.buyer_master set email = (select email from ( select bt.email, bt.buyer_ref_id from buyer_master_test bt, buyer_master b where b.buyer_ref_id = bt.BUYER_REF_ID) EMAILS where buyer_master.buyer_ref_id = EMAILS.BUYER_REF_ID)

    Error is returned as :
    SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
    TABLEID=3, COLNO=8" is not allowed. SQLSTATE=23502

    buyer_master.email is NOT NULL column.

    i checked none of the record of query "select email from ( select bt.email, bt.buyer_ref_id from buyer_master_test bt, buyer_master b where b.buyer_ref_id = bt.BUYER_REF_ID" is coming NULL

    why such error is thrown ? any idea please

    thanks in advance
    ssumit

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What was returned from the query?

    Code:
    select bt.email
         , bt.buyer_ref_id
     from  buyer_master_test  bt
         , SCRAP.buyer_master b
     where b.buyer_ref_id = bt.BUYER_REF_ID

  3. #3
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks Tonkuma,

    The output of query


    select bt.email, bt.buyer_ref_id from buyer_master_test bt,SCRAP.buyer_master b where b.buyer_ref_id = bt.BUYER_REF_ID

    is 348 rows containing emailids, buyer_ref_id none of the value is NULL.

    please suggest.
    ssumit

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your update statement is updating every row of the table. If the subquery does not match anything, it will return NULL, hence you are getting the error.

    Andy

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Pay attention to Andy. Instead of the join from Tonkuma change to an outer join and see the difference then, did nulls suddenly appear?
    Dave

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that Andy and Dave hit the point.

    Then my ideas were
    Code:
    update SCRAP.buyer_master AS t
     set   email
         = (select bt.email
             from  buyer_master_test bt
             where bt.BUYER_REF_ID = t.buyer_ref_id
           )
     WHERE EXISTS
           (select 0
             from  buyer_master_test bt
             where bt.BUYER_REF_ID = t.buyer_ref_id
           )
    ;
    or

    Code:
    MERGE SCRAP.buyer_master AS t
     INTO buyer_master_test  AS bt
      ON  bt.BUYER_REF_ID = t.buyer_ref_id
    WHEN MATCHED THEN
    UPDATE
     SET  email
        = bt.email
    ;

Posting Permissions

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