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
 sele
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
                    where 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
 
SQL> 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
  3                       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
 
SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-430077/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-430077/