Optimize update-virtual table and update-virtual
Before modification: A1update gls_voucher t set t. promptval = (select sum (m. originalval) from gls_vchitem m where m. vid = t. vid and m. dir = 1); B1update 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 ));
After modification: a1' update (select n. yr, n. promptval,. 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 =. 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 =. viid group by p. vid) q on n. vid = q. vid) t set t. promptexplain = t. newExplain;
-- The following statement will report the error ORA-01779: Unable to modify and non-key value save table corresponding column description: gls_voucher creden (vid primary key) and gls_vchitem creden (viid primary key) through vid Association objective: update the credenpropromptexplain = the value of the first explain in the creden under this creden that is not empty
Cause: after joining the second inner join, oracle cannot determine that gls_voucher n and gls_vchitem p are one-to-one queries. Change them to B1 above to 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 =. vid inner join gls_vchitem p on. viid = p. viid) t set t. promptexplain = t. newExplain