before change:A1Update Gls_voucher TSet t.promptval = (select sum (m.originalval)From gls_vchitem mwhere m.vid = T.vidand M.dir = 1);B1Update Gls_voucher TSet t.promptexplain = (select Max (n.explain)From gls_vchitem nwhere n.vid = T.vidand n.viid =(select min (m.viid)From gls_vchitem mwhere m.vid = N.vidAnd M.explain is not null));
after the change:A1 'Update (select n.yr, N.promptval, A.originalval newval From gls_voucher n Inner join (select m.vid, sum(m.originalval) OriginalvalFrom 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) explainFrom Gls_vchitem p Inner join (select m.vid, min(m.viid) viidFrom 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;
--The statement below will errorORA-01779: Cannot modify a column corresponding to a non-key-value Save tableDescription:Gls_voucher Voucher (vid primary Key)with theGls_vchitem Voucher Entry (VIId PRIMARY Key)throughvid correlationPurpose: Update voucherpromptexplain= The first clause in the voucher entry under the voucherexplain is not empty.Explain value
cause: After a second inner join association, Oracle cannot determinegls_voucher N andGls_vchitem P is a one-to-one query, changed to the above B1 ' on the lineUpdate (select N.yr, N.promptexplain, P.explain newexplain from Gls_voucher n inner join (select M.vid, M In (M.VIID) viid from Gls_vchitem m where m.explain are 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.newe Xplain
Optimizing update-Virtual Tables
