记录oracle 关联更新的例子

1、说明

修改某一天“本期值”数据时，需要把第二天的“上期值”更新。

 

2、sql如下，作为备忘

update (select * from JN_VEGETABLEDAYREPORT_ITEM a,JN_VEGETABLEDAYREPORT b where a.REPORTID=b.ID) t1 
set (WHOLESALEPRICESQ,WHOLESALEAMOUNTSQ,TURNOUTSQ,LANDINGSSQ,STOCKSQ) = 
(select WHOLESALEPRICE,WHOLESALEAMOUNT,TURNOUT,LANDINGS,STOCK 
from JN_VEGETABLEDAYREPORT_ITEM t2 join JN_VEGETABLEDAYREPORT t3 on t2.REPORTID=t3.ID 
where t1.RPTDATE=t3.RPTDATE+1 and t1.ENTERID=t3.ENTERID and t1.TARGETID =t2.TARGETID) 
where t1.ENTERID='79828' and t1.RPTDATE=to_date('2011-09-21','yyyy-MM-dd') + 1

 说明：按天更新

 

 

update (select * from JN_VEGETABLEWEEKREPORT_ITEM a,JN_VEGETABLEWEEKREPORT b where a.REPORTID=b.ID) t1 
set (PLANTAREASQ,TURNOUTSQ,LANDINGSSQ,STOCKSQ) = 
(select PLANTAREA,TURNOUT,LANDINGS,STOCK 
from JN_VEGETABLEWEEKREPORT_ITEM t2 join JN_VEGETABLEWEEKREPORT t3 on t2.REPORTID=t3.ID 
where t1.RPTDATE=t3.RPTDATE+7 and t1.ENTERID=t3.ENTERID and t1.TARGETID =t2.TARGETID) 
where t1.ENTERID='79831' and t1.RPTDATE=to_date('2011-09-09','yyyy-MM-dd')+7

 

说明：按周更新