|
|
ORA-01407: cannot update (string) to NULL tips
Oracle Error Tips by Donald Burleson(S. Karam)
|
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;