Oracle sql merge to insert and delete but not update Oracle sql merge to insert and delete but not update oracle oracle

Oracle sql merge to insert and delete but not update


No, you cannot delete rows that have not been updated by the merge command.
Here is documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

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. Any delete triggers defined on the target table will be activated for each row deletion.

That means, that rows must be updated. Hovewer, you don't need to update all rows, after UPDATE use the same WHERE clause as you are using after DELETE

when matched then update set dummy=dummy    where a_value not in ('ace','afk')delete     where a_value not in ('ace','afk')


I have found you can set the column to itself:

MERGE ...WHEN MATCHED THEN    UPDATE SET a_value = a_value WHERE a_value not in ('ace','afk')   DELETE WHERE a_value not in ('ace','afk')

This negates the need for the dummy column.