self merge in oracle 11g

Question

Usually there are cases when we need to perform an update or insert into the same table using two different queries. I wanted to see if this could be done on a table using a merge statement.

All I want to know if this can be done or not. Otherwise I'll have to stick with separating the query back into an update/insert operation separately.

Here is what I have so far:

METHOD 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')

At first this method made sense to me, because I would always be returning results from the source, and I'd update and insert accordingly. However, oracle refuses to follow this:

SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "TARGET"."EFF_FISCAL_YR_PD_NBR" 38104. 00000 - "Columns referenced in the ON Clause cannot be updated: %s" *Cause: LHS of UPDATE SET contains the columns referenced in the ON Clause

METHOD 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')

The logic behind this is, if I try to look up values from the source table, and it matches, it'll find the records and update itself with those values. However, the issue comes when trying to insert if it doesn't match. Because the source is filtered, no records get returned, therefore there's nothing for the target to match on, and nothing gets inserted. What I would like this to do is insert if no record is found in the SOURCE (implicitly not matched against the target), especially since the insert statement contains nothing by values passed in from variables rather than the source itself.

I've tried updating the source to look like this:

                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

But the problem with this is that the merge does the update on the record it matched AND an insert on the record it didn't match.

For those of you who want to know why I'm using a ROWID. This is because the design (not by me) indicated that COLA and COLB would be combined primary key that would be used as an index on the table. Duplicates of COLA, COLB, and COLC are not allowed but they are all updateable via the front end interface. I understand the pitfalls of ROWID, but because I'm only working with one table as target and source, regardless of any CRUD operations I perform on the table, the ROWID will always be matched onto itself.

Summary: I got the self merge to work only when performing an update on a matching item, but inserting doesn't work.

sksallaj · Answer 1 · 2013-11-06T16:28:22.670

Wow this took me a long while to do!

I was on the right track going with method 3 (UNION ALL with a null recordset from dual).

You just need to satisfy three conditions:

You always need to return a resulting set from the source table, but in a way where it wouldn't match with the target.
You can't return both a matching set and a non matching set otherwise you'll do both, an insert and an update
Your primary key is updateable because it matches on multiple columns. I have unique constraints on them so it'll throw an error if I try to make duplicates

So, here is what the source should look like:

            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

So you return one record. If the where clause is satisfied, you order the dataset in ASCENDING ORDER, and return only the top recordset. That way the merge will update based on that. If the where clause (not the one containing the ROWNUM) returns zero values, it'll still return the null recordset and the merge will insert based on that.

More than one record

If you really want to get nutty and get more than one record (in my case, I needed 1), then you have to get a count of the matching record set, using an aggregate (or an analytical function) and stuff it into a variable so that the where clause criteria looks like this:

WHERE ROWNUM <= COUNTOFRETURNEDRESULTS

@sksallaj- Thank you very much for this explanation and this is what exactly i was look in for. but i have a small issue here. when i ran this query from order by clause am getting 'COLA' as invalid identifier. is this order by required from dual? since we are getting only one record i do understand that we need to make it order by ASC. Please help me out here and it seems no one addressed this such scenario in merge statement.. — Sam, Sep 10 '16 at 09:02

seanmk · Answer 2 · 2013-11-06T00:58:06.893

1

If I understand you correctly COLA, COLB, and COLC are the composite primary key of TABLEA.

If that's the case, you don't actually need to use ROWID here, and can do what you need by just selecting from dual, then using your composite key in your ON statement like you did in the first attempt.

You don't need to update the primary key columns, so it's okay use them in the ON clause.

      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')

edited Nov 06 '13 at 00:58

answered Nov 06 '13 at 00:18

seanmk

1,934
15
28

Yeh this doesn't help because the update statement would only update CREATEDATE and USERID, what if I need to update any of COLA, COLB, or COLC? Like this: UPDATE TABLEA SET COLA='1' WHERE COLA='2'. It took me a while, but I think I found a hack while I was driving home. It wasn't apparent to me while looking at the code. – sksallaj Nov 06 '13 at 05:09
1

Updating primary keys is almost always a bad idea. The whole idea of a primary key is that it should define the things that are unique to a record and it should not change. It is unfortunate that you have been left with a schema where you need to do this! – seanmk Nov 06 '13 at 18:40
I agree. The DBA contractor announced it's easier to maintain. I brought up the point of separating the information about the index from the actual data otherwise it'd make querying very complex. In my case, ROWID is sufficient since it's doing a query onto itself. If I was to use a join, that's where problems would come up. I upvoted your answer since it is correct format for a self merge on an architecturally sound design. – sksallaj Nov 06 '13 at 19:10

score 0 · Answer 3 · answered Mar 03 '14 at 15:35

merge into MY_TARGET t using (select 1 from DUAL) s on (t.COL1 = :p1 ) when matched then update set t.COL3 = :p3 when not matched then insert (COL1, COL2, COL3) values (:p1, :p2, :p3)

you must have something to return in order to make insert

self merge in oracle 11g

3 Answers3

Linked