ORA-01407 Error

[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

[jodie]

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

[gandolf989]

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?

[ssmith001]

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

[jmodic]

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);

[ssmith001]

Thanks....that worked.

[mariag]

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

[PAVB]

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