CODEWITHSUNDEEP
sqloracle-databasemergeoracle11gr2
Luke SpringWalker
Luke SpringWalker

Reputation: 1592

Oracle - Subqueries reduction in Merge

I'm having a MERGE with a subquery to get and ID, and I'm wondering if the subquery in the NOT MATCHED statement is always executed or not. 

        MERGE INTO CAR_STOCK st
        USING CAR_PRODUCTO pro
        ON (pro.id = st.producto_id AND pro.ean = ?)
        WHEN MATCHED THEN
        UPDATE SET st.stockActual = ?
        WHEN NOT MATCHED THEN
        INSERT (stockActual, local_id, producto_id, activo)
        VALUES (?, ?, (SELECT id FROM car_producto WHERE ean = ?), 'S');

Thanks!

EDIT: the ? are because I'm using JDBC in a PreparedStatement.

Upvotes: 0

Views: 239

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

No need for subquery, You could simply refer to pro.id in insert part, like here:

merge into t1 using t2 on (t1.a = t2.a)
  when matched then update set t1.b = t2.b
  when not matched then insert (a, b)
  values (0, t2.b)

Test data and merge result:

create table t1 as (select 1 a, 'a01' b from dual);
create table t2 as (select 1 a, 'a05' b from dual union all select 2 a, 'bxx' b from dual );

     A  B
------  ---
     1  a05
     0  bxx

Upvotes: 1

Related Questions