摘要：
SQL˃setlinesize200SQL˃setpagesize200SQL˃select*froma；IDAMT--------------------1100220033004400SQL˃从b中选择*；IDAMT--------------------19921993299SQL˃explainplanforupdateaseta.amt=（selectb.amtfrombwh
SQL> set linesize 200
SQL> set pagesize 200
SQL> select * from a;
ID AMT
---------- ----------
1 100
2 200
3 300
4 400
SQL> select * from b;
ID AMT
---------- ----------
1 99
2 199
3 299
SQL> 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)