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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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(+);
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?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

cbridgmanAuthor Commented:
I tried the merge statement but got the following error:

SQL Error: ORA-00969: missing ON keyword
00969. 00000 -  "missing ON keyword"
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.