with as的update写法

update的with写法比较特殊。

错误的写法：

with sql1 as (select substr(sequence,5,28) as sequence ,

            checkresultflag from dz_resultfromboss_dz dz1 

           where dz1.innetcode=006

            and scpdateandtime>20130726000000 )

update sj_fileprocresult_0803 sj set sj.dzstatus = (select checkresultflag  from sql1 

           where sj.sequence=sequence
             );

 

上述写法会报ORA-00928: 缺失 SELECT 关键字

修改如下：

SQL> update sj_fileprocresult_0803 sj set sj.dzstatus = (
           with sql1 as (select substr(sequence,5,28) as sequence ,

            checkresultflag from dz_resultfromboss_dz dz1 

           where dz1.innetcode=006

            and scpdateandtime>20130726000000 )

            select checkresultflag  from sql1 

           where sj.sequence=sequence
             );