基于匹配ID的Oracle SQL更新表

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.

我试图基于MASTER_CUSTOMER的GPID字段更新表DIM_TRADING_ACCOUNTS的GPID字段，其中MASTER_CUSTOMER中的MCUST_CODE等于DIM_TRADING_ACCOUNTS的TRADING_CODE，无论我做什么，每次都会得到一个基本的语法错误。

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?

我一直在接受这个问题的指导。到目前为止我所做的四次尝试都是错误的。有人能帮我找到合适的oracle SQL语法吗?

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

注意:我在mcust_code中使用了upper，因为trading_code字段都是大写的

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

尝试1(错误是:SQL命令未正确结束)

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)

尝试2(错误为:缺少“SET”关键字)

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)

尝试3(这个返回错误:单行子查询返回多个行)

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)

尝试4(错误是:缺少“ON”关键字)

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;

2 个解决方案

#1

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

您应该在子查询中使用max或min，以避免单行子查询返回多个行错误。

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并检查gpid和tradingcode组合的结果。

 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.

如果您从上面的select中看到重复的行，您可以确保在更新中使用max或min。

#2

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.

Attemp 3在语法上是正确的，问题是表中的数据:似乎通过trading_code过滤的master_customer返回了不止一行。这是正确的吗?在这种情况下，没有办法进行您正在寻找的更新，因为只有一个trading_code可以关联不同的gpid值。

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

解决方案:只有在所有链接记录只有一个gpid值时才进行更新:

 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;

#1