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.

Two questions: 1) What ist "the constraint in my table" (and error message) you mentioned?Without the union in you source, the ON clause will always find a match - except when [ROWIDs change](http://stackoverflow.com/questions/435109#435123) - and no insertions will happen. 2) In which situation do you want the insert to happen? — halfbit, Sep 10 '16 at 10:29
May be the condition **tgt.rowid = src.rid** is satisfied in all cases — Jim Macaulay, Sep 10 '16 at 11:11
Please see the edited question with more details above. Thank you very much for your comments. — Sam, Sep 10 '16 at 12:57
Are you perhaps using ROWID as the name of a column in one of your tables? — Bob Jarvis - Слава Україні, Sep 11 '16 at 03:32

score 3 · Answer 1 · answered Sep 10 '16 at 18:09

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.

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

'uuuu' has been introduced in the same check.

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.

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.

Thank you for your help here. I have couple of concerns here. What will happend when table grow up with thousands of records? Then we have to put all those parameters in the IN clause which is not possible. Also if table has 20 columns or so performance will be really low in such cases. Appreciate your help here. — Sam, Sep 11 '16 at 01:56
Is the requirement like this - 1) if there are already 1000 records, it will insert 1001 th record. Then when the query runs again, it will update all of those 1001 records ? 2) What is the source of the values in 'values' list ? — SubhasisM, Sep 12 '16 at 04:52
yes, it might be 1000 records for this table. and values list will pass from outside ( CSV file ) . i will loop through it and if any existing column value matches with corresponding row in the CSV , need to update other columns in the table ( except the primary key columns) with the value set from the CSV. — Sam, Sep 12 '16 at 05:30
You can load the data from the csv file into a temporary table first. Then select the required values from that temporary table in the 'IN' list. You can use the same temporary table in the 'VALUES' list of the INSERT query. — SubhasisM, Sep 12 '16 at 10:09

Bob Jarvis - Слава Україні · Answer 2 · 2016-09-11T12:30:15.780

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.

Thanks for the reply but in my case i need to check each and every column in the select clause. that means need to check with OR condition.if any value matches i need to go ahead and update that particular record. Appreciate your help bob. Thank you — Sam, Sep 12 '16 at 06:11

score 1 · Accepted Answer · answered Sep 12 '16 at 07:47

I was able to make this work on below query.

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

        WHERE myTable.myRef = '2' or
        myTable.mytablename = 'sam'
      ) as rid from dual)  src
ON (tgt.rowid = src.rid)
WHEN MATCHED THEN
update set tgt.myRef = 'uuuu',tgt.mytablename='test1', tgt.name='nameA'
when not matched then 
insert (
tgt.mytableid,tgt.mytablename,tgt.name,tgt.myRef) values (11,'RRRR1','1','2');

first time it will insert the row and for subsequent runs it will update the row.

Oracle Merge statement not inserting data

3 Answers3