CODEWITHSUNDEEP
sqloracle-database
Scott Holtzman
Scott Holtzman

Reputation: 27239

Update Table Based on Multi-Join GroupBy SubQuery

I have the following Select written against an Oracle database which works:

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;

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.

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.

Way 1

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);

Way 2

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;

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.

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.

Upvotes: 4

Views: 157

Answers (1)

user5683823
user5683823

Reputation:

Updates like this are often easier to write, read, and maintain with the MERGE statement. Oracle documentation: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

merge into work_commission_summary_final e
   using [**your subquery here**]        subqry1
   on (     subqry1.account    = e.account
        and subqry1.sm_name    = e.sm_name
        and subqry1.timeperiod = e.timeperiod
   )
when matched
   then update e.bonus       = subqry1.bonus,
               e.maintenance = subqry1.maintenance;

Upvotes: 8

Related Questions