mysql - JOIN nested subquery returning NULL while updating calculated value -

i'm trying work around "you can't specify target table update in clause" mysql error, means i've got nested subquery (temp table). note, i'm trying select work before move on actual update.

what i've got ledger table i'm trying find associated row's calculated unit total using active_units (what row started with) , each additional adjustment in active units (there 1 payment row , multiple adjustment rows in same table associated commission id , schedule number). these grouped month_num. if it's active_units = 18 , there 3 adjustment rows, 1 active_units_chg = -2, should end 18 + -2 = 16.

when this:

select    active_units  , active_units_chg  , active_units_total  , crm_commission_payments.active_units + (     select sum(active_units_chg)     crm_commission_payments cp     cp.crm_commissions_item_id = crm_commission_payments.crm_commissions_item_id     , cp.schedule_a_no = crm_commission_payments.schedule_a_no     , cp.month_num = crm_commission_payments.month_num     , cp.item_active = 1 ) active_units_cal1 crm_commission_payments crm_commission_payments.payment_type = 'payment' , crm_commission_payments.crm_quotes_item_id = 2457 

active_units_cal1 correct row. however, when nested join'd subquery, null active_units_calc.calc_chg:

select    active_units  , active_units_chg  , active_units_total  , crm_commission_payments.active_units + (     select sum(active_units_chg)     crm_commission_payments cp     cp.crm_commissions_item_id = crm_commission_payments.crm_commissions_item_id     , cp.schedule_a_no = crm_commission_payments.schedule_a_no     , cp.month_num = crm_commission_payments.month_num     , cp.item_active = 1 ) active_units_cal1  , active_units_calc.calc_chg crm_commission_payments left join  (      select         source.calc_chg       , source.crm_commissions_item_id       , source.schedule_a_no       , source.month_num      crm_commission_payments cp1      inner join (           select              sum(active_units_chg) calc_chg            , cp.crm_commissions_item_id            , cp.schedule_a_no            , cp.month_num           crm_commission_payments cp           cp.item_active = 1      ) source      source.crm_commissions_item_id = cp1.crm_commissions_item_id      , source.schedule_a_no = cp1.schedule_a_no      , source.month_num = cp1.month_num ) active_units_calc on (    active_units_calc.crm_commissions_item_id = crm_commission_payments.crm_commissions_item_id    , active_units_calc.schedule_a_no = crm_commission_payments.schedule_a_no    , active_units_calc.month_num = crm_commission_payments.month_num ) crm_commission_payments.payment_type = 'payment' , crm_commission_payments.crm_quotes_item_id = 2457 

what doing wrong?