DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ORA-01407 Error

  1. #1
    Join Date
    Aug 2003
    Posts
    100

    ORA-01407 Error

    I am trying to run the following query and I get the error listed below. I cannot disable the Not NULL contraint, and I have verified that neither of the tables DFU tables contain null values in the DISC column. Any ideas would be apprecited.


    update dfu a
    set a.disc = (select b.disc
    from dfu@MANPROD.RAR.NCSUS.JNJ.COM b
    where a.dmdunit = b.dmdunit
    and a.dmdgroup = b.dmdgroup
    and a.loc = b.loc)


    The following error has occurred:

    ORA-01407: cannot update ("STSC"."DFU"."DISC") to NULL

  2. #2
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Your select is returning nothing (NULL) and the DISC column of the DFU table has a NOT NULL constraint.

    Run your select again to make sure you are returning values correctly.

    HTH

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: ORA-01407 Error

    Originally posted by ssmith001
    I am trying to run the following query and I get the error listed below. I cannot disable the Not NULL contraint, and I have verified that neither of the tables DFU tables contain null values in the DISC column. Any ideas would be apprecited.


    update dfu a
    set a.disc = (select b.disc
    from dfu@MANPROD.RAR.NCSUS.JNJ.COM b
    where a.dmdunit = b.dmdunit
    and a.dmdgroup = b.dmdgroup
    and a.loc = b.loc)


    The following error has occurred:

    ORA-01407: cannot update ("STSC"."DFU"."DISC") to NULL
    Does your sub query acutally return data?
    Are you updating every row in dfu with the results of the query,
    or do you need a where clasue on the update part of the statement?

  4. #4
    Join Date
    Aug 2003
    Posts
    100

    Response

    I basically need to update values from one table to another table, for each DFU where there is a match.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    update dfu a
    set a.disc = (select b.disc
    from dfu@MANPROD.RAR.NCSUS.JNJ.COM b
    where a.dmdunit = b.dmdunit
    and a.dmdgroup = b.dmdgroup
    and a.loc = b.loc)
    where exists
    (select null from dfu@MANPROD.RAR.NCSUS.JNJ.COM c
    where a.dmdunit = c.dmdunit
    and a.dmdgroup = c.dmdgroup
    and a.loc = c.loc);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2003
    Posts
    100

    Thanks

    Thanks....that worked.

  7. #7
    Join Date
    Apr 2008
    Posts
    1
    Hi

    I came across this post and it was the same situation as mine

    My update statement kept returning an 'ORA-01407' error even though I know for definite that the value I was updating with wasn't null

    I added in the extra which exists (select null.....) bit and it worked fine! Could someone please explain the logic behind how that makes it work please?


    Thanks!!!
    Maria

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    if a matching row exists then you update your target row otherwise you don't do it.

    You may want to read again about where exists
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width