update更新为空值的问题

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)