ORA-30926-合并状态 [英] ORA-30926 - Merge state

问题描述

我收到ORA-30926错误.我对此进行了研究，发现这通常是由USING子句中指定的查询中的重复项引起的.

I am getting an ORA-30926 error. I researched this and found that this is usually caused by duplicates in the query specified in theUSING clause.

问题是我最初要删除重复项并存储在温度表(temp_distinct)中，而温度表又是我在MERGE中引用的.这是我的代码段:

The problem is I am intially removing duplicates and storing in a temperory table (temp_distinct) which in turn I am referring to in the MERGE. Here is my code snippet:

MERGE INTO name_test nt 
USING (select name from temp_distinct) s 
ON (1=1)
WHEN MATCHED
THEN UPDATE SET nt.fn = s.name, nt.LN = s.name

这是我的表的结构:

NAME_TEST:

FN    LN
----- -----
Ruc   Rag
Ruc   Ran
Sam   Kum
Ruc   Ran
Ruc   Kum
Ran   Dev
Rag   Agar
Rag   Ran

TEMP_DISTINCT:

FN    NUMB NAME  NUM
----- ---- ----- ---
Sam   1    Mark  1
Rag   2    Steve 2
Dev   3    John  3
Kum   4    Dave  4
Ruc   5    Mich  5
Agar  6    Dean  6
Ran   7    Phil  7

因此您可以看到USING子句中没有重复项.我正在尝试替换NT.FN = S.NAME以及NT.LN = S.NAME.

So as you can see there is no duplicate in USING clause. I am trying to replace NT.FN = S.NAME and also NT.LN = S.NAME.

基本上我想用TEMP_DISTINCT表中的其他名称替换NAME_TEST表中的FN和LN中的名称.最终输出应如下所示:

Basically I want to replace names in FN and LN from NAME_TEST table with different name from TEMP_DISTINCT table. Final output should be like below:

FN     LN
------ ------
Mich   Steve
Mich   Phil
Mark   Dave
Mich   Phil
Mich   Dave
Phil   John
Steve  Dean
Steve  Ran

推荐答案

除了@zaratustra的解释之外，您的合并还尝试将fn和ln设置为相同的name值，因此即使它奏效也不会给出您想要的结果.尝试更新它们时，不能在using子句中使用fn或ln.

In addition to the explanation from @zaratustra, your merge is also attempting to set the fn and ln to the same name value so it wouldn't give the result you want even it worked. And you can't use fn or ln in the using clause as you're trying to update them.

如果您的name_test表具有主键(或至少具有唯一键)列，则可以将其包括在合并中，但是您仍然只能正确更新fn或ln值一口气.

If your name_test table had a primary key (or at least unique) column then you could include that in the merge, but you'd still only be able to correctly update either the fn or ln value in one pass.

我不确定您为什么不进行简单的更新:

I'm not sure why you're not doing a simple update:

update name_test nt
set fn = (select td.name from temp_distinct td where td.fn = nt.fn),
  ln = (select td.name from temp_distinct td where td.fn = nt.ln);

8 rows updated.

select * from name_test;

FN    LN  
----- -----
Mich  Steve 
Mich  Phil  
Mark  Dave  
Mich  Phil  
Mich  Dave  
Phil  John  
Steve Dean  
Steve Phil  

这篇关于ORA-30926-合并状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！