Update Table Based on Multi-Join GroupBy SubQuery
<p>I have the following Select written against an Oracle database which works:</p>

<pre><code>SELECT d.REGION_NAME REGION, 'SM' PLAN, b.TRADING_CODE ACCOUNT, c.RM_SM_NAME SM_NAME,'Q116' TIMEPERIOD,
  SUM(CASE WHEN a.PAYOUT_TYPE = 'BONUS' THEN a.PAYOUT END) as BONUS,
  SUM(CASE WHEN a.PAYOUT_TYPE = 'MAINTENANCE' THEN a.PAYOUT END) as MAINTENANCE
FROM FACT_COMM_LARGE_ACCT_BONUS a
INNER JOIN DIM_TRADING_ACCOUNTS b on b.trading_dwkey = a.trading_dwkey
INNER JOIN REF_RM_SM_REGION c on c.RM_SM_USER_CODE = a.RM_SM_USER_CODE
INNER JOIN REF_REGION d on d.REGION_DWKEY = c.REGION_DWKEY
GROUP BY d.REGION_NAME, 'SM', b.TRADING_CODE, c.RM_SM_NAME;
</code></pre>

<p>What I want to with the results of this data is Update the BONUS and MAINTENANCE fields in another table where the combination of ACCOUNT | SM_NAME | TIMEPERIOD are equal.</p>

<p>I have been playing around with the Update statement, but am getting stuck. I have tried using a SubQuery and WHERE Clause in two different ways join.</p>

<p><strong>Way 1</strong></p>

<pre><code>UPDATE WORK_COMMISSION_SUMMARY_FINAL e
    SET e.BONUS = (SELECT subqry1.BONUS FROM (
            SELECT d.REGION_NAME REGION, 'SM' PLAN, b.TRADING_CODE ACCOUNT, c.RM_SM_NAME SM_NAME,'Q116' TIMEPERIOD,
                SUM(CASE WHEN a.PAYOUT_TYPE = 'BONUS' THEN a.PAYOUT END) as BONUS,
                SUM(CASE WHEN a.PAYOUT_TYPE = 'MAINTENANCE' THEN a.PAYOUT END) as MAINTENANCE
            FROM FACT_COMM_LARGE_ACCT_BONUS a
            INNER JOIN DIM_TRADING_ACCOUNTS b on b.trading_dwkey = a.trading_dwkey
            INNER JOIN REF_RM_SM_REGION c on c.RM_SM_USER_CODE = a.RM_SM_USER_CODE
            INNER JOIN REF_REGION d on d.REGION_DWKEY = c.REGION_DWKEY
            GROUP BY d.REGION_NAME, 'SM', b.TRADING_CODE, c.RM_SM_NAME) AS subqry1
          WHERE subqry1.ACCOUNT = e.Account and subqry1.SM_NAME = e.SM_NAME and subqry1.TIMEPERIOD = e.TIMEPERIOD);
</code></pre>

<p><strong>Way 2</strong></p>

<pre><code>UPDATE WORK_COMMISSION_SUMMARY_FINAL e
    SET e.BONUS = subqry1.BONUS,
        e.MAINTENANCE = subqry1.MAINTENANCE
    (SELECT d.REGION_NAME REGION, 'SM' PLAN, b.TRADING_CODE ACCOUNT, c.RM_SM_NAME SM_NAME,'Q116' TIMEPERIOD,
      SUM(CASE WHEN a.PAYOUT_TYPE = 'BONUS' THEN a.PAYOUT END) as BONUS,
      SUM(CASE WHEN a.PAYOUT_TYPE = 'MAINTENANCE' THEN a.PAYOUT END) as MAINTENANCE
    FROM FACT_COMM_LARGE_ACCT_BONUS a
    INNER JOIN DIM_TRADING_ACCOUNTS b on b.trading_dwkey = a.trading_dwkey
    INNER JOIN REF_RM_SM_REGION c on c.RM_SM_USER_CODE = a.RM_SM_USER_CODE
    INNER JOIN REF_REGION d on d.REGION_DWKEY = c.REGION_DWKEY
    GROUP BY d.REGION_NAME, 'SM', b.TRADING_CODE, c.RM_SM_NAME) as subqry1
  WHERE subqry1.ACCOUNT = e.ACCOUNT and subqry1.SM_NAME = e.SM_NAME and subqry1.TIMEPERIOD = e.TIMEPERIOD;  
</code></pre>

<p>I am pretty new to more advanced SQL stuff and am pretty stuck here. I am probably over-complicating the way I am trying to go about it, but if anyone can lend a hand, I'd appreciate it.</p>

<p>Last thing to note is that this will most likely be part of a larger procedure, so if I need to break into steps, that is a possible solution.</p>