1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Cannot update (%s) to null error

Discussion in 'SQL PL/SQL' started by tranzeo, Sep 7, 2018.

  1. tranzeo

    tranzeo Starter

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    vancouver
    The following select query is to join 2 tables to give me the list of employees who are terminated:
    select a.YEAN8, a.YEHMCU, b.YAALPH, b.YADT, b.YAPTDT , a.YEPSDT, a.YEPTDT
    from CRPDTA.F060118 a, CRPDTA.F060116 b
    where a.YEAN8 = b.YAAN8 and a.YEHMCU = 229 and b.YAPAST = 'T';
    [​IMG]

    Goal is to populate last column 'YEPTDT' to be the same as 'YADT.

    Wrote the following update query:
    update CRPDTA.F060118
    set YEPTDT =
    (select b.YADT from CRPDTA.F060116 b
    where CRPDTA.F060118.YEAN8 = b.YAAN8 and b.YAHMCU = 229 and b.YAPAST = 'T');

    However, got the following error:
    Error report -
    SQL Error: ORA-01407: cannot update ("CRPDTA"."F060118"."YEPTDT") to NULL
    01407. 00000 - "cannot update (%s) to NULL"

    Now I know that column 'YEPTDT' does not allow NULL. However, as I verified in my first select statement that there are values in 'YADT' and no NULL.
    Any idea how to get around it?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If F060116 has the sane definition as F060118 then shouldn't b.YAAN8 be b.YEAN8?