Oracle Merge statement not inserting data

Question

I have came up with below sql statement on oracle 11g for merge the data.

  MERGE INTO myTable tgt
USING ( SELECT myTable.ROWID AS rid
         FROM   myTable

        WHERE myTable.myRef = 'uuuu' or
        myTable.name = 'sam'
        --union
        --select rowId,null from dual
      ) src
ON (tgt.rowid = src.rid)
WHEN MATCHED THEN
update set tgt.myRef = 'uuuu',tgt.name='name_worked'
when not matched then 
insert (
tgt.myRef,tgt.name) values ('RRRR','HHH');

I need to insert data except ID column here which will manage from the trigger for primary key insertion. due to error ORA-38104: Columns referenced in the ON Clause cannot be updated i used RowId approach here.

now my issue is merge statement works fine when it comes to update but fail in inserting data in this situation.

I went through this post and added the union statement. but still it fails when goes to insert duplicate record due to the constraint in my table instead of running it smoothly.

Can anyone please help me out here? Appreciate it a lot. thank you in advanced.

==========Edited===========

Please find the complete code sample and the error messages below.

MERGE INTO myTable tgt
USING ( SELECT myTable.ROWID AS rid
         FROM   myTable

        WHERE myTable.myRef = 'RRRR' or
        myTable.mytablename = 'sam'
        union
        select rowId from dual
      ) src
ON (tgt.rowid = src.rid)
WHEN MATCHED THEN
update set tgt.myRef = 'uuuu',tgt.mytablename='myt_name', tgt.name='nameA'
when not matched then 
insert (
tgt.mytableid,tgt.mytablename,tgt.name,tgt.myRef) values (11,'RRRR','HHH','myref1');

and my table is -

CREATE TABLE "sa"."MYTABLE"
  (
    "MYTABLEID"   NUMBER NOT NULL ENABLE,
    "MYTABLENAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "NAME"        VARCHAR2(20 BYTE),
    "MYREF"       VARCHAR2(20 BYTE),
    CONSTRAINT "MYTABLE_PK" PRIMARY KEY ("MYTABLEID", "MYTABLENAME")
  )

if i run this first time it will insert the record as expected. when i run it the second time my expectation is it should match the myRef = 'RRRR' and do the update. i intentionally put 'or' between condition because if i find any value exist in the table it should go and update the existing record.

but instead of doing that update it will throw this error because merge statement try to insert again.

SQL Error: ORA-00001: unique constraint (sa.MYTABLE_PK) violated
00001. 00000 -  "unique constraint (%s.%s) violated"

my requirement is when it run the first time it should insert and when i run the same again it should pick that record and update it. Please let me know what to adjust in my merge statement in order to work as expected here. Thanks in advance.

Answer 1

The below MERGE query is what you are looking for.

MERGE INTO myTable tgt
USING ( select x.rid from 
       (SELECT myTable.ROWID AS rid
        FROM   myTable 
        WHERE myTable.myRef IN ('myref1','uuuu')) x 
        right outer join dual 
        on x.rowid <> dual.rowid
      ) src
ON (tgt.rowid = src.rid)
WHEN MATCHED THEN
update set tgt.myRef = 'uuuu',tgt.mytablename='myt_name', tgt.name='nameA'
when not matched then 
insert (tgt.mytableid,tgt.mytablename,tgt.name,tgt.myRef) values (mytable_seq.nextval,'RRRR','HHH','myref1');

There have been 3 changes made from the query that you provided.

1. Inside the 'using' subquery 'RRRR' was being checked in myTable.myRef column, but while inserting 'myref1' was being inserted in the same. Hence this has been changed in the using subquery to check 'myref1'.
2. 'uuuu' has been introduced in the same check.
3. DUAL tas been introduced in right outer join.

The query will process as below -

1.During first run, there would be no row in mytable. Hence the right outer join with dual will prevail. This will enable the insert to happen.

2. During 2nd run, there will be a row with myRef column of 'myref1'. Its Rowid will be picked up and the Update will happen. After update, myRef column will be updated to 'uuuu'.

3.During all subsequent runs, 1 row will always be returned in the inside using subquery, this time because of the column value of 'uuuu'. This will enable the update to happen , which will again update the columns with the same existing values.

Thus in effect, 1st time INSERT will happen and in all subsequent times UPDATE will take place.

Answer 2

It appears you want something like the following:

MERGE INTO MYTABLE t
  USING (SELECT newID, newTable_name from DUAL) d
    ON (t.MYTABLEID = d.newID AND
        t.MYTABLENAME = d.newTable_name)
  WHEN MATCHED THEN
    UPDATE SET NAME = newName,
               MYREF = newRef
  WHEN NOT MATCHED THEN
    INSERT (MYTABLEID, MYTABLENAME, NAME, MYREF)
    VALUES (newID, newTable_name, newName, newRef)

where the variables newID, newTable_name, newName, and newRef are populated from whatever source of data you're using.

Also - do you really want the primary key to be (MYTABLEID, MYTABLENAME)? Can you actually have multiple rows with the same MYTABLEID value? And do you really want to allow multiple rows having the same MYTABLENAME value? My thought is that the primary key should be MYTABLEID, with a UNIQUE constraint on MYTABLENAME. If this is the case then MYTABLEID is redundant, and MYTABLENAME could be used as the primary key. ????

Best of luck.