update更新为空值的问题

来源：互联网发布：海岛奇兵医药包数据编辑：程序博客网时间：2024/04/28 13:58
SQL> set linesize 200SQL> set pagesize 200SQL> select * from a;ID  AMT---------- ---------- 1  100 2  200 3  300 4  400SQL> select * from b;ID  AMT---------- ---------- 1   99 2  199 3  299SQL> explain plan for update a set a.amt =(select b.amt from b  where a.id=b.id)  2  ;Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1561808831---------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | UPDATE STATEMENT   |  |4 |   104 |    19  (22)| 00:00:01 ||   1 |  UPDATE    | A  |  |  |       |  ||   2 |   TABLE ACCESS FULL| A  |4 |   104 |3   (0)| 00:00:01 ||*  3 |   TABLE ACCESS FULL| B  |1 |    26 |3   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("B"."ID"=:B1)Note-----   - dynamic sampling used for this statement (level=2)19 rows selected.当A表ID=4的时候,那么B.ID=4 返回空,此时A表就被更新为空正确写法:update a set a.amt =(select b.amt from b  where a.id=b.id)where a.id in (select b.id from b)
0 0