I want a table with another table. But one of the columns is mapped in the key table and you need to use already new values. Cannot use subquery.

The code is now:

MERGE INTO TABLE_2 ST USING (SELECT * FROM TABLE_1 FT LEFT JOIN KEY_TABLE ksubs ON ksubs.PK_ID = ft.PK_ID) joined ON (ksubs.NEW_PK_ID = ST.PK_ID AND FT.COL_2 = <константа>) WHEN MATCHED THEN UPDATE SET ST.COL_3 = FT.COL_3

Mistake in:

 FT.COL_2 = <константа>

FT.COL_2 is an invalid identifier (ORA-00904: invalid identifier).

Is it even possible to use a subquery as a source for MERGE?

  • one
    You gave an alias to joined . Use it, not FT . The same about ksubs - Viktorov
  • Yes, I already did, but this is not enough. It didn’t work with an asterisk; it was launched only when I indicated clearly which columns are needed in the subquery and gave each one a unique name. Now it is executed (this is a long time), but it seems to be running. - Vitaly Yandulov
  • 2
    You love to write select *. , and I always considered this a vicious practice =) - Viktorov
  • one
    And it is still not clear why the condition on FT.COL_2 with a constant is written in the phrase ON. That if the optimizer itself does not realize that this condition can be started already in the subquery before the left join is executed, the query can turn out to be hundreds of times slower if there are a lot of records and it will start doing left join even for those records that you throw away in ON - Mike
  • Let us write this into my bad habits — although the scheme is a test one, so with the other values ​​almost all the records are cleaned, but I will pay attention to it. - Vitaly Jandulov

1 answer 1

Yes you can. As a source in a MERGE statement, you can specify a table, view, or the result of a subquery.

USING Clause
Updated or inserted. This can be a subquery.

Regarding the error in the question, in the ON condition it is necessary to indicate the alias of the entire subquery, the aliases defined inside the subquery are not visible here.

 create table source as select rownum id, 'src '||rownum name from xmlTable ('1 to 3') ; create table target as select * from source where 1=0; create view sview as select * from source;

All three requests are valid:

 merge into target t using source s on (t.id = s.id) when not matched then insert values (s.id, s.name) ; merge into target t using sview s on (t.id = s.id) when not matched then insert values (s.id, s.name) ; merge into target t using (select * from source) s on (t.id = s.id) when not matched then insert values (s.id, s.name) ;

and give the same result:

3 rows merged.