How to Fix ORA-01407: Cannot Update to NULL in Oracle

ORA-01407 means an UPDATE tried to set a NOT NULL column to NULL. Sometimes the NULL is written in the statement, but more often it comes from an expression or a subquery that found nothing, or found a row whose value is NULL. The fix is to keep the old value, supply a fallback, or skip rows that have no new value.

Reproduce the Error

Example:

create table demo_accounts (account_id number primary key, owner varchar2(30) not null, email varchar2(60));
insert into demo_accounts values (1, 'Ana', 'ana@example.com');
insert into demo_accounts values (2, 'Raj', null);
commit;

update demo_accounts set owner = null where account_id = 1;
update demo_accounts set owner = (select email from demo_accounts where account_id = 2) where account_id = 1;

Output:

Table DEMO_ACCOUNTS created.

1 row inserted.

1 row inserted.

Commit complete.

Error starting at line : 6
In command -
update demo_accounts set owner = null where account_id = 1
Error at Command Line : 6 Column : 26
Error report -
SQL Error: ORA-01407: cannot update ("NIMBUS"."DEMO_ACCOUNTS"."OWNER") to NULL

Error starting at line : 7
In command -
update demo_accounts set owner = (select email from demo_accounts where account_id = 2) where account_id = 1
Error at Command Line : 7 Column : 26
Error report -
SQL Error: ORA-01407: cannot update ("NIMBUS"."DEMO_ACCOUNTS"."OWNER") to NULL

The first UPDATE sets NULL directly. The second copies the email of account 2, which is NULL, into owner.

Fix: Keep the Old Value

NVL falls back to the current value when the new one is NULL:

Example:

-- keep the old value when the new one is missing
update demo_accounts
set    owner = nvl((select email from demo_accounts where account_id = 2), owner)
where  account_id = 1;
select * from demo_accounts order by account_id;

Output:

1 row updated.

   ACCOUNT_ID OWNER    EMAIL
_____________ ________ __________________
            1 Ana      ana@example.com
            2 Raj

The update ran, and owner kept Ana because the new value was NULL.

Other Ways

• Update only rows that have a new value, with WHERE EXISTS or a MERGE that matches on non-NULL values.
• A correlated subquery that finds no row returns NULL; add a WHERE condition so rows without a match are not updated.

Related Guides

• How to Fix ORA-01400: Cannot Insert NULL in Oracle
• How NULL Works in Oracle Conditions

Conclusion

ORA-01407 means an UPDATE produced NULL for a NOT NULL column. Find where the NULL comes from, then keep the old value with NVL, provide a fallback, or limit the update to rows that have a value.