Oracle MERGE语句错误(ORA-30926) [英] Oracle MERGE statement error (ORA-30926)

问题描述

我继承了在Oracle 11g上运行的Oracle脚本，该脚本包含以下MERGE语句:

I inherited an Oracle script running on Oracle 11g that contains the following MERGE statement:

MERGE INTO MainTable PR
     USING IncrementalTable PRA
       ON (PR.contract = PRA.contract
       and PRA.memberType = 'Parent' )
     WHEN MATCHED THEN
       UPDATE SET PR.address1 = PRA.address1,
                  PR.city = PRA.city,
                  PR.state = PRA.sate,
                  PR.zipCode = PRA.zipCode,
                  PR.countryCode = PRA.countryCode
WHERE address1 IS NULL
  AND PR.memberType <> 'Parent'
;

据我所知，这只是从IncrementalTable中父项的地址更新MainTable中子项的地址.不幸的是，当我运行该语句时，它引发以下错误:

As far as I can see, this is simply updating the child's address in MainTable from the parent's address in IncrementalTable. Unfortunately, when I run the statement, it throws the following error:

ORA-30926:无法在源表中获得稳定的行集

ORA-30926: unable to get a stable set of rows in the source tables

因此，似乎它无法在IncrementalTable中找到用于更新的独特匹配项.查询数据似乎是这种情况:

So, it looks like it can't find a distinct match in IncrementalTable with which to update. Querying the data, that appears to be the case:

select contract,
       memberType,
       count(*)
  from IncrementalTable
 group by contract,
       memberType
having count(*) > 1
;

CONTRACT               MEMBERTYPE   COUNT(*)
---------------------- ---------- ----------
1119839490             PARENT              2
7271122516             PARENT              2
1004798721             PARENT              2

查看其中一项合同的详细信息:

Looking at the details of one of those contracts:

select *
  from IncrementalTable
 where contract = '1119839490'
   and memberType = 'Parent'
;


CONTRACT               MEMBERTYPE ADDRESS1          CITY                           STATE ZIPCODE   COUNTRYCODE
---------------------- ---------- ----------------  ------------------------------ ----- -------   -----------
1165439488             Parent     1234 Dorioth St   Orlando                        FL    32825     USA
1165439488             Parent     1234 Dorioth St   Orlando                        FL    32825     USA

那么，如何合并IncrementalTable中的DISTINCT匹配项?

So, how can I merge only the DISTINCT match from IncrementalTable?

感谢您的帮助！

推荐答案

第一个(也是最明显的)答案是清理数据，以免重复.您提供的示例数据似乎是错误的结果.

The first (and most obvious) answer is to clean up the data so you don't have duplicates. It seems likely that the sample data you provided is the result of a bug.

对于merge语句，如果数据实际上是完全重复的，则可以使用distinct:

As for the merge statement, if the data is actually purely duplicated, you can just use distinct:

MERGE INTO maintable pr
USING      (SELECT DISTINCT contract,
                            membertype,
                            address1,
                            city,
                            state,
                            zipcode,
                            countrycode
            FROM   incrementaltable pra
            WHERE  pra.membertype = 'Parent')
ON         (pr.contract = pra.contract)
WHEN MATCHED THEN
   UPDATE SET pr.address1 = pra.address1,
              pr.city = pra.city,
              pr.state = pra.sate,
              pr.zipcode = pra.zipcode,
              pr.countrycode = pra.countrycode
      WHERE      address1 IS NULL AND pr.membertype <> 'Parent'

这篇关于Oracle MERGE语句错误(ORA-30926)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！