自我合并在oracle 11g中-Java 学习之路

通常情况下，我们需要使用两个不同的查询执行更新或插入同一个表 . 我想看看是否可以使用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')

起初这个方法对我来说很有意义，因为我总是会从源返回结果，并且我会相应地更新和插入 . 但是，oracle拒绝遵循这个：

SQL错误：ORA-38104：无法更新ON子句中引用的列：“TARGET” . “EFF_FISCAL_YR_PD_NBR”38104. 00000 - “ON子句中引用的列无法更新：％s”*原因：UPDATE SET的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')

这背后的逻辑是，如果我尝试从源表中查找值并且匹配，它将找到记录并使用这些值更新自身 . 但是，如果尝试插入不匹配，则会出现问题 . 由于源被过滤，因此没有返回任何记录，因此目标没有任何内容可以匹配，也没有任何内容被插入 . 我想要做的是插入如果在SOURCE中没有找到记录（隐式地不与目标匹配），特别是因为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的人 . 这是因为设计（不是由我）表明COLA和COLB将组合在一起用作表格索引的主键 . 不允许复制COLA，COLB和COLC，但它们都是 updateable via the front end interface . 我理解ROWID的缺陷，但因为我只使用一个表作为目标和源，无论我在表上执行任何CRUD操作，ROWID将始终匹配到自身 .

简介：我只在对匹配项执行更新时才进行自我合并，但插入不起作用 .

3 回答

如果我理解正确，COLA，COLB和COLC是TABLEA的复合主键 .

如果是这种情况，你实际上并不需要在这里使用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')

回复于 2024-04-25T04:16:07+08:00

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

我正在使用方法3进行正确的操作（UNION ALL使用双重的空记录集） .

你只需要满足三个条件：
- 您总是需要从源表返回结果集，但是它与目标不匹配 .
- 您可以't return both a matching set and a non matching set otherwise you' ll同时执行插入和更新
- 您的主键是可更新的，因为它匹配多个列 . 我对它们有唯一的约束，所以如果我尝试复制它会引发错误
所以，这是源应该是什么样子：
```
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子句，则在ASCENDING ORDER中对数据集进行排序，并仅返回顶部记录集 . 那样合并将 update 基于此 . 如果where子句（不是包含ROWNUM的子句）返回零值，它仍将返回空记录集，并且合并将基于此 insert .

More than one record

如果你真的想要获得坚果并获得多个记录（在我的情况下，我需要1），那么你必须使用聚合（或分析函数）得到匹配记录集的计数并将其填入变量，以便where子句标准如下所示：
```
WHERE ROWNUM <= COUNTOFRETURNEDRESULTS
```
回复于 2024-04-25T04:16:07+08:00
0

合并到MY_TARGET t时使用（从DUAL中选择1）s（t.COL1 =：p1）匹配后更新设置t.COL3 =：p3不匹配时插入（COL1，COL2，COL3）值（：p1，： p2，：p3）

你必须要有东西才能进行插入

回复于 2024-04-25T04:16:07+08:00

自我合并在oracle 11g中

3 回答

相关问题