-
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
Quote:
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);
-
Thanks
-
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