The documentation for MERGE says, "Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted.
This doesn't appear to be what's happening. Here is an example
>create table mergetest ( id number, val number(7,2));
Table created.
>merge into mergetest using dual
on ( id = 1 )
when not matched then insert ( id, val ) values ( 1, 5 )
when matched then update set val = val - 5
delete where val = 0;
1 row merged.
>select * from mergetest;
ID VAL
---------- ----------
1 5
>merge into mergetest using dual
on ( id = 1 )
when not matched then insert ( id, val ) values ( 1, 5 )
when matched then update set val = val - 5
delete where val = 0;
1 row merged.
>select * from mergetest;
ID VAL
---------- ----------
1 0
As I read the documentaion, this line was updated by the WHEN MATCHED clause, then the DELETE WHERE clause should have deleted it since val now matches the condition. Doing the same merge again, deletes the row, which means DELETE WHERE is looking at the value before the WHEN MATCHED clause runs, not after.
>merge into mergetest using dual
on ( id = 1 )
when not matched then insert ( id, val ) values ( 1, 5 )
when matched then update set val = val - 5
delete where val = 0;
1 row merged.
>select * from mergetest;
no rows selected
Am I misunderstanding how this is supposed to work or is this working incorrectly?
Version 11.0.2.4 on 64-bit Linux.