Merge error in Oracle 11g

Question

I'm new at Oracle 11g and this forum. I just need to run a simple update statement using Oracle Merge statement because my SQL developer won't take it any other way.

The strange thing is, no matter how I try correcting the statement, even after reading many entries in this forum I still got the exact same error messages.

There are 2 different error messages, depend on how I run my statements in SQL Developer. If I run my statement as a store procedure (the update statement is a small part within a store procedure. I commented out other SQL statements and just left this update statement within the stored procedure) I got this error:

I ran this:

create or replace
PROCEDURE ADDR_UPDATE_2
AS
/*     other sql statements are commented out    */  

MERGE INTO (SELECT id,xseq,line_1,line_2,zip,tax FROM ADDR ) b
USING (SELECT id,xseq,line_1, line_2,NVL(zip, '') AS pos_10 , NVL(tax, '') AS pos_11
     FROM TEMP_ADDR ) v
ON(b.id = v.id
  AND b.xseq = v.xseq)  
WHEN MATCHED THEN UPDATE SET b.line_1 = v.line_1,
                          b.line_2 = v.line_2,                            
                          b.zip = v.pos_10,
                          b.tax = v.pos_11
                          WHERE b.line_1 <> v.line_1  
                          OR b.line_2 <> v.line_2
                          OR b.zip <> v.pos_10; 

/*     other sql statements are commented out   */

END;

and I got this error:

Error(23,9): PLS-00103: Encountered the symbol "INTO" when expecting one of the following: constant exception table long double ref char time timestamp interval date binary national character nchar

(I goggled everywhere and did not find any answer)

line 23 refers to the word "INTO"

• But if I ran the above Merge statement by itself, NOT as part of a str. proc., I don't get any error. I got a result: 0 rows merged.

Now I modified the Merge statement into:

create or replace
PROCEDURE ADDR_UPDATE_2
AS

/*  other sql statements are commented out  */  

MERGE INTO ADDR b
USING (SELECT id,xseq, line_1, line_2, NVL(zip, '') AS pos_10, 
            NVL(tax, '') AS pos_11 
     FROM TEMP_ADDR) v
 ON(b.id = v.id
    AND b.xseq = v.xseq
    AND b.line_1 <> v.line_1  
    OR b.line_2 <> v.line_2 
    OR  b.zip <> v.pos_10)  
 WHEN MATCHED THEN UPDATE SET b.line_1 = v.line_1,
                          b.line_2 = v.line_2,
                          b.zip = v.pos_10,
                          b.tax = v.pos_11;

 /*
   other sql statements are commented out
 */

END;

I got the same error:

Error(23,9): PLS-00103: Encountered the symbol "INTO" when expecting one of the following: constant exception table long double ref char time timestamp interval date binary national character nchar

line 23 refers to the word "INTO"

When I ran the statement not as part of str. proc:

I got a different error:

Error at Command Line:9 Column:8
Error report: SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "B"."LINE_2" 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 *Action:

I have tried looking for answer since 2 days ago with no result, is there anyone can help how to solve this problem?

Answer 1

The following MERGE statement should at least compile and execute properly, although I'll agree with @APC that the right thing to do here is to use an UPDATE (because that's what you're really trying to do):

MERGE INTO ADDR b
USING TEMP_ADDR v
  ON (b.id = v.id AND
      b.xseq = v.xseq)  
WHEN MATCHED THEN
  UPDATE
    SET b.line_1 = v.line_1,
        b.line_2 = v.line_2,                            
        b.zip = v.zip,
        b.tax = v.tax;

I elminated the NVL manipulations you were doing because in Oracle a zero-length string (e.g. '') is the same as NULL, so the NVL calls were equivalent to saying "If this field is not NULL, return it - otherwise return NULL" which is sort of pointless.

Best of luck.

Answer 2

As I explained in the question referenced above, 'Columns referenced in the ON Clause cannot be updated' means that we cannot update columns in the MATCHED clause if they are included in the ON criteria.

You are in this position because you are trying to use MERGE to do something which it is not intended to do. Now you say

"I just need to run a simple update statement using Oracle Merge statement because my SQL developer won't take it any other way."

Fnord. There is absolutely no way SQL Developer will prevent you running an update statement provided you get the syntax right. Unfortunately Oracle doesn't support the ANSI join syntax in DML statements (unlike other flovours of RDBMS), and the Oracle syntax is not intuitive:

update
(
   select v.id
          , v.xseq
          , v.line_1
          , v.line_2
          , nvl(v.zip, '') AS pos_10
          , nvl(v.tax, '') AS pos_11
   from   addr b
         ,temp_addr v
   where  ( b.id = v.id
            and b.xseq = v.xseq )
      and ( b.line_1 <> v.line_1  
            or  b.line_2 <> v.line_2 
            or  b.zip <> v.pos_10)
     )
set b.line_1 = v.line_1,
    b.line_2 = v.line_2,
    b.zip = v.pos_10,
    b.tax = v.pos_11
;