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"."JPDURATION") 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.
Who is Participating?
johnsoneSenior Oracle DBACommented:
If you want to use update instead of merge, then I would go with:
UPDATE jobplan JP 
SET    JP.jpduration = (SELECT TMP.laborhrs 
                        FROM   tmp_hours TMP 
                        WHERE  JP.jpnum = TMP.jpnum) 
WHERE  jpnum IN (SELECT jpnum 
                 FROM   tmp_hours); 

Open in new window

The subselect in the original query doesn't carry over to the outer select.  You are updating every row in JOBPLAN regardless of whether a row exists in TMP_HOURS.  The added where clause to the update statement will limint the update to only rows in TMP_HOURS.
slightwv (䄆 Netminder) Commented:
My guess is that without a WHERE clause on the update, you are updating ALL rows in the table and something there is causing the ORA-01407.

Try MERGE instead of your update:
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

Open in new window

Gerwin Jansen, EE MVETopic Advisor Commented:
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(+);
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cbridgmanAuthor Commented:
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?
cbridgmanAuthor Commented:
I tried the merge statement but got the following error:

SQL Error: ORA-00969: missing ON keyword
00969. 00000 -  "missing ON keyword"
slightwv (䄆 Netminder) Commented:
The above update hits the table twice.  This might cause performance issues.

I missed some parans

Try this:
merge into jobplan JP
using (
	select jpnum, laborhrs
	from tmp_hours
on (JP.jpnum = TMP.jpnum)
when matched then update set jp.jpduration=tmp.laborhrs

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.