Oracle SQL Update Table Based on Matching ID

Question

I am attempting to update the GPID field of table DIM_TRADING_ACCOUNTS based on the GPID field of MASTER_CUSTOMER where the MCUST_CODE in MASTER_CUSTOMER is equal to the TRADING_CODE of DIM_TRADING_ACCOUNTS and no matter what I try, I get what basically amounts to an syntax error each time.

I have been taking guidance from this question. The four attempts I have made so far are below along with errors. Can someone please help me get the proper syntax for oracle SQL that works?

Note: I used upper on mcust_code because the trading_code field is all upper-case

Attempt 1 (error is: SQL command not properly ended)

UPDATE dim_trading_accounts dta
  SET dta.gpid = mc.gpid
FROM master_customer mc
WHERE UPPER(mc.mcust_code) = dta.trading_code;

Attempt 2 (error is: missing "SET" keyword)

UPDATE 
  dim_trading_accounts dta, master_customer mc
SET
  dta.gpid = mc.gpid
WHERE
  upper(mc.mcust_code) = dta.trading_code;

Attempt 3 (this one returns error: single-row subquery returns more than one row)

UPDATE dim_trading_accounts dta
SET dta.gpid = (SELECT mc.gpid
                FROM master_customer mc
                WHERE dta.trading_code = upper(mc.mcust_code))
WHERE EXISTS (SELECT 1
              FROM master_customer mc
              WHERE dta.trading_code = upper(mc.mcust_code));

Attempt 4 (error is: missing "ON" keyword)

MERGE INTO dim_trading_accounts
  USING master_customer
    ON dim_trading_accounts.trading_code = upper(master_customer.mcust_code)
WHEN MATCHED THEN
  UPDATE
    SET dim_trading_accounts.gpid = master_customer.gpid;

Vamsi Prabhala · Accepted Answer · 2016-07-15T15:49:44.090

2

You should use max or min in the sub-query to avoid single-row subquery returns more than one row error.

UPDATE 
  dim_trading_accounts dta
SET
  dta.gpid = (SELECT MAX(gpid) 
              FROM master_customer 
              WHERE upper(mcust_code) = dta.trading_code);

To see why the error comes up, use a select and check the results for gpid and tradingcode combination.

 SELECT mc.gpid, dta.trading_code
 FROM master_customer mc
 JOIN dim_trading_accounts dta ON upper(mc.mcust_code) = dta.trading_code

If you see duplicate rows from the select above, you can be sure to use max or min in the update.

edited Jul 15 '16 at 15:49

answered Jul 15 '16 at 15:36

Vamsi Prabhala

48,685
4
36
58

yes, I discovered this and the interesting thing is that i filtered out the `mccust_code` with multiple `gpid` and i still get the error. *(I filtered them out because I want to update the rest and check the multiple ones manually)* – Scott Holtzman Jul 15 '16 at 16:13
nevermind, I found the other two offenders that I missed by accident :) ... thanks for the help :) I think i got it straight now :) ... even though I didn't use the `Max` solution because I need to be specific about what gets updated, I will choose this answer since it got me in the right direction. – Scott Holtzman Jul 15 '16 at 16:32
You shouldn't find the offenders manually. You should search for them within their own table, using `count distinct(gpid)` and `group by mcust_code`. In your `update` statement you should use a subquery where you filter out the offending rows; for example with a "group by" and `having count distinct(gpid) = 1`. – Jul 15 '16 at 16:40

score 2 · Answer 2 · answered Jul 15 '16 at 15:38

Attemp 3 is correct syntactically, the problem is the data in the table: seems that master_customer filtered by trading_code returns more than one row. Is this correct? In this case there's no way to make the update you're looking for because to one single trading_code there can be associated different gpid values.

Workaround: update only in case there's exactly one single gpid value for all linked records:

 UPDATE dim_trading_accounts dta
 SET dta.gpid = (SELECT MIN(mc.gpid)
                FROM master_customer mc
                 WHERE dta.trading_code = upper(mc.mcust_code))
 WHERE (SELECT COUNT(DISTINCT gpid)   
               FROM master_customer mc
              WHERE dta.trading_code = upper(mc.mcust_code)) = 1;

Oracle SQL Update Table Based on Matching ID

2 Answers2