Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    DB2 sql - null values not allowed

    Hello,

    i need help with following update SQL:

    update table1 a set a.IDPHNO = (select b.IRPHNO from table2 b where
    a.IDSUNO = b.IRSUNO and b.IRSUNO <> '') where a.IDPHNO<> ''
    -> Error: null values not allowed (ID SQL0407)

    I also tried following:

    update table1 a set a.IDPHNO = (select b.IRPHNO from
    table2 b where a.IDSUNO = b.IRSUNO and b.IRPHNO <>'')
    where exists
    (select * from
    table2 b where a.IDSUNO = b.IRSUNO and b.IRPHNO <>'')
    -> more than one row (ID SQL0811)

    Can someone help?
    Thanks, Chatman

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    495
    There is a NOT NULL constraint defined on IDPHNO column. You may add to the WHERE clause:
    AND b.IRPHNO IS NOT NULL
    Of course, I do not know what your business requirements are.
    Florin Aparaschivei
    DB2 9.7 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Oct 2012
    Posts
    11
    Another way, if you want to update all records in your select criteria, is to use a case statement so that if b.IRPHNO is null, you return a dummy value.

  4. #4
    Join Date
    Nov 2012
    Posts
    2

    not NULL

    Hello, thanks for your feedback but i think this do not work to add NULL in query:

    update table1 a set a.IDPHNO =
    (
    select b.IRPHNO from table2 b
    where a.IDSUNO = b.IRSUNO and b.IRPHNO <> '' and b.IRPHNO is not NULL
    )
    where a.IDPHNO <> '' and a.IDPHNO is not NULL

    Any idea?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,449
    If the subquery doesn't return any rows, your UPDATE statement will attempt to assign NULL to a.IDPHNO
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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