优化update-虚拟表,优化update-虚拟
改前: A1 update gls_voucher t set t.promptval = (select sum(m.originalval) from gls_vchitem m where m.vid = t.vid and m.dir = 1) ; B1 update gls_voucher t set t.promptexplain = (select max(n.explain) from gls_vchitem n where n.vid = t.vid and n.viid = (select min(m.viid) from gls_vchitem m where m.vid = n.vid and m.explain is not null)) ;改后: A1' update (select n.yr, n.promptval, a.originalval newVal from gls_voucher n inner join (select m.vid, sum(m.originalval) originalval from gls_vchitem m where m.dir = 1 group by m.vid) a on n.vid = a.vid) t set t.promptval = t.newVal ; B1' update (select n.yr, n.promptexplain, q.explain newExplain from gls_voucher n inner join (select p.vid, max(p.explain) explain from gls_vchitem p inner join (select m.vid, min(m.viid) viid from gls_vchitem m where m.explain is not null group by m.vid) a on p.viid = a.viid group by p.vid) q on n.vid = q.vid) t set t.promptexplain = t.newExplain ;
--下边这个语句会报错 ORA-01779: 无法修改与非键值保存表对应的列 说明: gls_voucher凭证(vid主键) 与 gls_vchitem凭证项(viid主键) 通过 vid 关联 目的: 更新凭证promptexplain=该凭证下凭证项中的第一条explain不为空的explain值
原因: 通过第二个inner join关联后,oracle不能确定gls_voucher n和gls_vchitem p是一对一的查询,改成上面B1'就行了 update (select n.yr, n.promptexplain, p.explain newExplain from gls_voucher n inner join (select m.vid, min(m.viid) viid from gls_vchitem m where m.explain is not null group by m.vid) a on n.vid = a.vid inner join gls_vchitem p on a.viid = p.viid) t set t.promptexplain = t.newExplain
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。