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>