Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Asd.

Asked: September 29, 2016 - 2:05 pm UTC

Last updated: September 30, 2016 - 7:43 am UTC

Version: 11g

Viewed 1000+ times

You Asked

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!

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

and Chris said...

You can check this with a simple test:

- Create a function in that outputs some text
- Call that in your subquery
- Run a merge that doesn't insert anything
- See if the text from the function is in your output

Let's see what happens when we do this.

Setup

create or replace function f
  return int as 
begin
  dbms_output.put_line('executed');
  return 1;
end;
/
create table t (
  x int,
 y int
);


Test

SQL> merge into t
  2  using  (select 1 n from dual) d
  3  on   (t.x = d.n)
  4  when matched then update set t.y = 2
  5  when not matched then insert values (n, (select f from dual));
executed

1 row merged.

SQL>
SQL> merge into t
  2  using  (select 1 n from dual) d
  3  on   (t.x = d.n)
  4  when matched then update set t.y = 2
  5  when not matched then insert values (n, (select f from dual));

1 row merged.


So the answer is no.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library