cbridgman
asked on
Oracle - Cannot Update To Null
I am attempting to set a value in one table to a value that I get from another table. The source table does not contain any null values in it but Oracle returns the following message each time I try.
SQL Error: ORA-01407: cannot update ("MXDB"."JOBPLAN"."JPDURAT ION") to NULL
01407. 00000 - "cannot update (%s) to NULL"
This is the SQL that I'm using:
update jobplan JP
set JP.jpduration =
(select TMP.laborhrs
from tmp_hours TMP
where JP.jpnum = TMP.jpnum);
The TMP_HOURS table has only two columns: jpnum and laborhrs. Neither column contains null in any row.
The JOBPLAN table has many columns, two of which are jpnum and jpduration.
I am trying to set jobplan.jpduration to TMP_HOURS.laborhrs.
There is a matching record in the JOBPLAN table for every record in the TMP_HOURS table.
Any ideas what is going on?
The laborhrs and jpduration fields are both float.
SQL Error: ORA-01407: cannot update ("MXDB"."JOBPLAN"."JPDURAT
01407. 00000 - "cannot update (%s) to NULL"
This is the SQL that I'm using:
update jobplan JP
set JP.jpduration =
(select TMP.laborhrs
from tmp_hours TMP
where JP.jpnum = TMP.jpnum);
The TMP_HOURS table has only two columns: jpnum and laborhrs. Neither column contains null in any row.
The JOBPLAN table has many columns, two of which are jpnum and jpduration.
I am trying to set jobplan.jpduration to TMP_HOURS.laborhrs.
There is a matching record in the JOBPLAN table for every record in the TMP_HOURS table.
Any ideas what is going on?
The laborhrs and jpduration fields are both float.
I'm thinking you have jpnum's in JP that are not in TMP, resulting in a null from the subquery, which is causing the error. Can you try this and see whether you get any NULL's:
select JP.jpnum, TMP.jpnum, JP.jpduration, TMP.laborhrs
from tmp_hours TMP, jobplan JP
where JP.jpnum = TMP.jpnum(+);
select JP.jpnum, TMP.jpnum, JP.jpduration, TMP.laborhrs
from tmp_hours TMP, jobplan JP
where JP.jpnum = TMP.jpnum(+);
ASKER
There are, in fact, many nulls. I expected that there would be but thought that the where clause in my query (JP.jpnum = TMP.jpnum) would have fixed that problem. What should I try next?
ASKER
I tried the merge statement but got the following error:
SQL Error: ORA-00969: missing ON keyword
00969. 00000 - "missing ON keyword"
SQL Error: ORA-00969: missing ON keyword
00969. 00000 - "missing ON keyword"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The above update hits the table twice. This might cause performance issues.
I missed some parans
Try this:
I missed some parans
Try this:
merge into jobplan JP
using (
select jpnum, laborhrs
from tmp_hours
) TMP
on (JP.jpnum = TMP.jpnum)
when matched then update set jp.jpduration=tmp.laborhrs
;
Try MERGE instead of your update:
Open in new window