ORA-01407: cannot update to null 错误解决

ORA-01407: cannot update to null 错误解决

在更新客户数据时 出现ORA-01407错误

SQL> select table_id,game_deal_log_id from tb_test_log;
 
TABLE_ID   GAME_DEAL_LOG_ID
---------- ----------------
G                         0
10001               4923601
AA                  4923676
BB                  4923672
CC                  4923677
DD                  4923674
FF                  4923673
GG                  4923671
HH                  2616668
II                  4923668
C                         0
D                         0
E                         0
F                         0

14 rows selected

SQL> select * from test;

GAME_DEAL_LOG_ID TABLE_ID
---------------- ----------
4661667 10001
4661556 AA
4661558 BB
4661559 CC
4661553 DD
4661552 FF
4661555 GG
2616668 HH
4661557 II

9 rows selected

SQL>
SQL> update tb_test_log a
  2     set game_deal_log_id = (select game_deal_log_id
  3                       from test b
  4                      where a.table_id = b.table_id);

update tb_adjustment_handledeal_log a
   set game_deal_log_id = (select game_deal_log_id
                            from test b
                            here a.table_id = b.table_id)
ORA-01407: cannot update ("TEST"."TB_TEST_LOG"."GAME_DEAL_LOG_ID") to NULL

这是因为上面sql运行时 会更新所有game_deal_log_id字段 下面我删除不相等的table_id
 
QL> delete from tb_test_log where table_id in ('C','D','E','F','G');

5 rows deleted
 
SQL>
SQL> update tb_test_log a
  2     set game_deal_log_id = (select game_deal_log_id
                                 from test b
  4                      where a.table_id = b.table_id);
 
9 rows updated

更新成功     这个sql就只能保证两边数据记录一致 才能更新成功

SQL> select table_id,game_deal_log_id from tb_test_log;
 
TABLE_ID   GAME_DEAL_LOG_ID
---------- ----------------
10001               4661667
AA                  4661556
BB                  4661558
CC                  4661559
DD                  4661553
FF                  4661552
GG                  4661555
HH                  2616668
II                  4661557

9 rows selected
 
SQL> rollback;

Rollback complete

SQL>

所以上面sql还是写的有问题,没有真正关联到相等的table_id,采取如下写法就是正确的写法
SQL> update tb_test_log a
 2     set game_deal_log_id = (select game_deal_log_id
 3                       from test b
 4                      where a.table_id = b.table_id)
 5  where  exists (select 1 from test b where a.table_id=b.table_id);

9 rows updated

SQL> select table_id,game_deal_log_id from tb_test_log;

TABLE_ID   GAME_DEAL_LOG_ID
---------- ----------------
G                         0
10001               4661667
AA                  4661556
BB                  4661558
CC                  4661559
DD                  4661553
FF                  4661552
GG                  4661555
HH                  2616668
II                  4661557
C                         0
D                         0
E                         0
F                         0

14 rows selected