Error: Cannot update <field> to Null

[lesrobot]

Hi, I am trying to update a field, in rows, in an existing table, with the contents of a field from another table, and can't get past this error.. ora-01407: cannot update ("GEMMS"."CM_CMPT_DTL"."CMPNT_COST") to NULL.

This is the SQL:

update cm_cmpt_dtl a
set a.cmpnt_cost =  
  (select b.cmpnt_cost
  from nt_cmpt_upd b
  where a.item_id = b.item_id
  and a.item_id = 6
  and a.cost_mthd_code = 'MKT'
  and a.whse_code = '918'
  and a.calendar_code = '2008'
  and a.period_code = 7)

This is the data in the tables:

cm_cmpt_dtl
ITEM_ID WHSE_CODE CALENDAR_CODE PERIOD_CODE COST_MTHD_CODE CMPNT_COST
6 858 2008 7 MKT 21.12

nt_cmpt_upd
ITEM_ID WHSE_CODE CMPNT_COST
6 918 19.632
9 918 13.68
10 918 15.215834
11 918 21.423639
15 918 6.0650805

Any ideas where I am going wrong...??

Thanks,

Les

 

[r937]

perhaps you could ask this question in the oracle forum?

r937.com | rudy.ca

 

[JarlH]

Is the column cm_cmpt_dtl.cmpnt_cost declared as nullable?

I guess the SELECT returns NULL for at least one of the rows in cm_cmpt_dt table.

 

[PHV]

What about this ?

update cm_cmpt_dtl a
set a.cmpnt_cost =  
  (select b.cmpnt_cost
  from nt_cmpt_upd b
  where a.item_id = b.item_id)
where a.item_id = 6
  and a.cost_mthd_code = 'MKT'
  and a.whse_code = '918'
  and a.calendar_code = '2008'
  and a.period_code = 7

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SQLSister]

The code below should get you past the error message, but you need to investigate the records where the cost is currently null to see if there is a problem if it should not ever be null.

update cm_cmpt_dtl a
set a.cmpnt_cost =  
  (select b.cmpnt_cost
  from nt_cmpt_upd b
  where a.item_id = b.item_id
  and a.item_id = 6
  and a.cost_mthd_code = 'MKT'
  and a.whse_code = '918'
  and a.calendar_code = '2008'
  and a.period_code = 7 and .cmpnt_cost is not null)

"NOTHING is more important in a database than integrity." ESquared

 

[lesrobot]

I checked the contents, of cmpnt_cost, and could not find any null values...will also try the Oracle Forum...Thanks..

 

[PHV]

Did you try my suggestion ?

 

[lesrobot]

PHV,

Thanks for your fix...it worked fine... I appreciate it...

Les