分享ORACLE 两表关联更新的两种方式

需求

目前L表的PK_NO对应N表的PACKNO,需要将N表的prepackicode更新为L表的PK_ID
select * from LPN_TEST L;
select * from ncic_lpn N;

实现方式

1、内联视图更新

UPDATE (
select L.PK_NO,L.PK_ID,N.PACKNO,N.prepackicode from LPN_TEST L,ncic_lpn N where L.PK_NO = N.PACKNO
)t
set T.prepackicode=T.PK_ID; 

2、merge更新

merge into (select N.PACKNO,N.prepackicode from ncic_lpn N) T
using LPN_TEST L
on (T.PACKNO = L.PK_NO)
when matched then 
  update set T.prepackicode=L.PK_ID;