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"."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.

slightwv (䄆 Netminder)

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

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

cbridgman

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?

cbridgman

ASKER

I tried the merge statement but got the following error:

SQL Error: ORA-00969: missing ON keyword
00969. 00000 - "missing ON keyword"

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

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
) TMP
on (JP.jpnum = TMP.jpnum)
when matched then update set jp.jpduration=tmp.laborhrs
;

Open in new window