Oracle 栏位级联修改

1、  方法1：

  UPDATE PUR_PO p1

   set (a, b) =

       (select p1.a - p2.total_amount, p1.b + p2.total_amount

          from PUR_ACCEPT p2

         where p1.form_no = p2.po_no

           and p2.FORM_NO = 1021

           AND p2.ACCEPT_TYPE_VALUE = 1)

 where exists (select *

          from PUR_ACCEPT p2

         where p1.form_no = p2.po_no

           and p2.FORM_NO = 1021

           AND p2.ACCEPT_TYPE_VALUE = 1);

2、  方法2：

 Merge into PUR_PO A

    Using (SELECT TOTAL_AMOUNT, PO_NO

             FROM PUR_ACCEPT

            WHERE FORM_NO = 1021

              AND ACCEPT_TYPE_VALUE = 1) B

    on (A.FORM_NO = B.PO_NO)

    when Matched then

      Update set A.a = A.a - B.TOTAL_AMOUNT, A.b = A.b + b.TOTAL_AMOUNT;