甲骨文11g中的自合并

Question

通常情况下，我们需要使用两个不同的查询执行更新或插入到同一个表中。我想看看是否可以在使用merge语句的表上完成这一任务。

我只想知道这件事能不能做到。否则，我将不得不继续将查询分离回更新/插入操作。

以下是我到目前为止所拥有的：

方法1：

          MERGE INTO TABLEA TARGET
          USING (
              SELECT 1 FROM DUAL
          ) SOURCE
          ON (TARGET.TARGET.COLA = '001'
              AND TARGET.TARGET.COLB = '1111111'
              AND TARGET.COLC = '201302'
              )
          WHEN MATCHED THEN
             UPDATE SET TARGET.COLA = '001'
                        ,TARGET.COLB = '1111111'
                        ,TARGET.COLC = '201304'
                        ,TARGET.CREATEDATE = SYSDATE
                        ,TARGET.USERID = 'USERA'
          WHEN NOT MATCHED THEN
             INSERT (TARGET.COLA
                     ,TARGET.COLB
                     ,TARGET.COLC
                     ,TARGET.COLD
                     ,TARGET.CREATEDATE
                     ,TARGET.USERID)
             VALUES('001'
                    ,'1111111'
                    ,'201304'
                    ,'123'
                    ,SYSDATE
                    ,'USERA')

起初，这个方法对我来说是有意义的，因为我总是从源返回结果，并相应地进行更新和插入。然而，甲骨文拒绝这样做：

SQL : ORA-38104: ON子句中引用的列不能更新：“EFF_FISCAL_YR_PD_NBR”.“目标”38104。00000 -“ON子句中引用的列不能更新：%s”*原因:更新集的LHS包含ON子句中引用的列。

方法2：

          MERGE INTO TABLEA TARGET
          USING (
                SELECT ROWID AS RID,COLA,COLB,COLC
                FROM TABLEA
                WHERE COLA = '001'
                      AND COLB = '1111111'
                      AND COLC = '201301'
          ) SOURCE
          ON (TARGET.ROWID = SOURCE.RID)
          WHEN MATCHED THEN
             UPDATE SET TARGET.COLA = '001'
                        ,TARGET.COLB = '1111111'
                        ,TARGET.COLC = '201304'
                        ,TARGET.CREATEDATE = SYSDATE
                        ,TARGET.USERID = 'USERA'
          WHEN NOT MATCHED THEN
             INSERT (TARGET.COLA
                     ,TARGET.COLB
                     ,TARGET.COLC
                     ,TARGET.COLD
                     ,TARGET.CREATEDATE
                     ,TARGET.USERID)
             VALUES('001'
                    ,'1111111'
                    ,'201304'
                    ,'123'
                    ,SYSDATE
                    ,'USERA')

这背后的逻辑是，如果我试图从源表中查找值并与其匹配，它将找到记录并使用这些值更新自己。但是，如果不匹配，则尝试插入时会出现此问题。因为源被过滤，所以不返回记录，因此目标没有匹配的东西，也没有插入任何记录。我希望这样做的是，如果在源中没有找到任何记录(隐式地与目标不匹配)，特别是因为insert语句不包含从变量传入的值，而不包含源本身。

我尝试更新源代码，如下所示：

                SELECT ROWID AS RID,COLA,COLB,COLC
                FROM TABLEA
                WHERE COLA = '001'
                      AND COLB = '1111111'
                      AND COLC = '201301'
                UNION ALL
                SELECT ROWID,NULL,NULL,NULL FROM DUAL

但问题在于，合并会对它匹配的记录进行更新，并在它不匹配的记录上插入。

想知道我为什么要用ROWID的人。这是因为设计(不是我设计的)表明，可乐和可乐将合并主键，将用作表上的索引。不允许复制可乐、可乐和COLC，但它们都是通过前端接口更新的。我理解ROWID的缺陷，但是因为我只使用一个表作为目标和源，所以不管我在表上执行什么CRUD操作，ROWID本身都会被匹配。

摘要:只有在对匹配项执行更新时，才能使self工作，但是插入不起作用。

Answer 1

哇，这花了我很长时间才做的！

我在正确的轨道上使用方法3(记录集从dual)。

你只需要满足三个条件：

1. 您总是需要从源表返回一个结果集，但以与目标不匹配的方式返回。
2. 不能同时返回匹配集和非匹配集，否则将同时执行插入和更新
3. 您的主键是可更新的，因为它在多个列上匹配。我对它们有唯一的约束，所以如果我尝试复制，它会抛出一个错误。

因此，下面是源代码应该是什么样子：

            SELECT RID,COLA,COLB,COLC FROM
            (
                SELECT ROWID AS RID,COLA,COLB,COLC
                FROM TABLEA
                WHERE COLA = '001'
                    AND COLB = '1111111'
                    AND COLC = '201301'
                UNION ALL
                SELECT ROWID,NULL,NULL,NULL FROM DUAL
                ORDER BY COLA ASC
            ) f
            WHERE ROWNUM <= 1

所以你还了一张唱片。如果满足where子句，则按升序排序数据集，并仅返回顶部记录集。这样，合并将在此基础上更新。如果where子句(而不是包含ROWNUM的子句)返回零值，它仍将返回空记录集，合并将在此基础上插入。

多个记录

如果您真的想抓狂并获得多条记录(在我的例子中，我需要1)，那么您必须使用聚合(或分析函数)获取匹配记录集的计数，并将其填充到变量中，以便where子句条件如下所示：

WHERE ROWNUM <= COUNTOFRETURNEDRESULTS

Answer 2

如果我对你的理解正确，可乐、可乐和COLC是表A的综合主键。

如果是这样的话，您实际上不需要在这里使用ROWID，只需要从dual中选择，然后在ON语句中使用复合键，就像第一次尝试那样。

您不需要更新主键列，所以可以在ON子句中使用它们。

      MERGE INTO TABLEA TARGET
      USING (
            SELECT '001' COLA,
                   '1111111' COLB,
                   '201301' COLC
            FROM DUAL
      ) SOURCE
      ON (TARGET.COLA = SOURCE.COLA
          AND TARGET.COLB = SOURCE.COLB
          AND TARGET.COLC = SOURCE.COLC
          )
      WHEN MATCHED THEN
         UPDATE SET TARGET.CREATEDATE = SYSDATE
                    ,TARGET.USERID = 'USERA'
      WHEN NOT MATCHED THEN
         INSERT (TARGET.COLA
                 ,TARGET.COLB
                 ,TARGET.COLC
                 ,TARGET.COLD
                 ,TARGET.CREATEDATE
                 ,TARGET.USERID)
         VALUES('001'
                ,'1111111'
                ,'201304'
                ,'123'
                ,SYSDATE
                ,'USERA')