oracle的ora 01407,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>