-
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
-
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
-
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?
-
Response
I basically need to update values from one table to another table, for each DFU where there is a match.
-
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?
-
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|