How do I delete with a merge statement? I want to update MERGE_DELETE_TEST2 to match MERGE_DELETE_TEST1 (think ETL). I cannot get merge delete to remove the row that exists in MERGE_DELETE_TEST2 that does not exist in MERGE_DELETE_TEST1.
The DELETE in a merge is not for non-existence. From the docs
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
Hence if you ETL is about update existing, insert new and delete non-existing, then the MERGE can do the first two, and then add a DELETE statement afterwards along the lines of:
delete from ...
from (col1,col2,col3) not in ( select c1,c2,c3 from ... )