oracle外关联更新操作,记要oracle 关联更新的例子

记录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

说明：按周更新