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
.
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.
Setupcreate or replace function f
return int as
begin
dbms_output.put_line('executed');
return 1;
end;
/
create table t (
x int,
y int
);
TestSQL> 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.