ORA-01407: cannot update (string) to NULL tips

翻译自：http://www.dba-oracle.com/t_ora_01407_cannot_update_string_to_null.htm

ORA-01407 这个错误发生在当你尝试把字段更改为NULL值，但是该列的字段不可以为NULL。

ORA-01407 occurs as you are trying to change a column to NULL when the column does not accept NULL values.

 

 

The Oracle docs note this on the ORA-01407 error:

ORA-01407 cannot update (string) to NULL 

Cause: An attempt was made to update a table column "USER"."TABLE"."COLUMN" with a NULL value.

For example, if you enter:

connect scott/tiger
update table a (a1 number not null);
insert into a values (null);

Oracle returns:

ORA-01407 cannot update ("SCOTT"."A"."A1") to NULL

which means you cannot update the column "SCOTT"."A"."A1" to NULL.

Action: Retry the operation with a value other than NULL.

ORA-01407 occurs as you are trying to change a column to NULL when the column does not accept NULL values.

To resolve ORA-01407, try correcting theeUPDATEEstatement to binsurethat a when a column is defined assNOT NULLL, there is no attempt toUPDATEEit with aaNULLLvalue. You may want to reference the Oracle documentation about ORA-01407 for an example..

In an update with a equality sub-select, one solution to the ORA-01407 error in SQL is to check for NULL rows using the where exists clause:

update ORDERS ord
set ord.amount = (select ord.qty * it.item_price 
from ITEM it 
where ord.item_id = it.item_id);

Error: ORA-01407: cannot update ("MYSCHEMA"."ORDERS"."AMOUNT") to NULL

This removes the ORA-01407 error:

update ORDERS ord 
set ord.amount = (select ord.qty * it.item_price 
from ITEM it 
where ord.item_id = it.item_id) 
where exists (select 1
from ITEM it 
where ord.item_id = it.item_id);
commit;