Oracle 11g error in merge using link on new record in insert statement

Question

I have two tables:

CREATE TABLE
    test
    (
        id INTEGER,
        str VARCHAR2(10)
    );
CREATE TABLE
    test2
    (
        id INTEGER,
        tmp VARCHAR2(10)
    );

and content:

INSERT INTO test2   (id,    tmp)    VALUES (    10,'tmp10');
INSERT INTO test2   (id,    tmp)    VALUES (    20,'tmp20');

When I running next script:

MERGE INTO  test mt
USING   (
        SELECT
            1  AS id,
            10 AS id2
        FROM
            dual) nr
ON  (   mt.id=nr.id)
WHEN MATCHED THEN
   UPDATE SET str=(SELECT   id  FROM test2  WHERE       id=nr.id2)
WHEN NOT MATCHED    THEN 
   INSERT   (id,str)    VALUES (    nr.id,(SELECT   tmp FROM    test2   WHERE   id=nr.id2) );

I see error:

[Error Code: 904, SQL State: 42000]  ORA-00904: "NR"."ID2": invalid identifier

But when I running:

MERGE INTO  test mt
USING   (
        SELECT
            1  AS id,
            10 AS id2
        FROM
            dual) nr
ON  (   mt.id=nr.id)
WHEN MATCHED THEN
   UPDATE SET str=(SELECT   id  FROM test2  WHERE       id=nr.id2)
WHEN NOT MATCHED    THEN 
   INSERT   (id,str)    VALUES (    nr.id,(SELECT   tmp FROM    test2   WHERE   id=10) );

it works.

Why is link on new record works in update statement, but not works in insert statement?

Answer 1

Try this ... note the change to the MERGE ...

1) in the USING clause, make sure you have a FULL query which returns the results you want ..

2) your INSERT will reference the value directly from there .. NOT from a sub query in the INSERT sub statement ..

  SQL> select * from test;

  no rows selected

  SQL> select * from test2;

          ID TMP
  ---------- ----------
          10 tmp10
          20 tmp20

  SQL> SELECT id, tmp
    2  FROM test2
    3   WHERE id IN ( 1, 10 )
    4  /

          ID TMP
  ---------- ----------
          10 tmp10

  SQL> MERGE INTO test mt
    2     USING (
    3        SELECT id, tmp
    4          FROM test2
    5         WHERE id IN ( 1, 10 )
    6         )  nr
    7     ON ( mt.id = nr.id )
    8     WHEN MATCHED THEN
    9        UPDATE set str = nr.tmp
   10     WHEN NOT MATCHED THEN
   11        INSERT ( id, str ) VALUES ( nr.id, nr.tmp )
   12  /

  1 row merged.

  SQL> commit;

  Commit complete.

  SQL> select * from test;

          ID STR
  ---------- ----------
          10 tmp10

  SQL>

Or to verify / test the INSERT portion:

  SQL> MERGE INTO test mt
    2     USING (
    3        SELECT id, tmp
    4          FROM test2
    5         WHERE id IN ( 1, 10, 20 )
    6         )  nr
    7     ON ( mt.id = nr.id )
    8     WHEN MATCHED THEN
    9        UPDATE set str = nr.tmp
   10     WHEN NOT MATCHED THEN
   11        INSERT ( id, str ) VALUES ( nr.id, nr.tmp )
   12  /

  2 rows merged.

  SQL> select * from test;

          ID STR
  ---------- ----------
          10 tmp10
          20 tmp20

  SQL>