Climbing up the oracle merge statement eventually

Wanted to write this post for ages… More than ever to record my findings and sample applications of this, in words, really really powerful sql statement with all its options and, well, probable pitfalls. Credits to be premetioned go to Tim @ oracle-base.com for the 9i introduction of merge (MERGE Statement) and the according 10g update (MERGE Statement Enhancements in Oracle Database 10g). I also used the latest docs @ Oracle being of 12c (MERGE), not revealing any newer functionality beyond 10g.

Ok so, what’s important to understand first? The basic idea? Well, from my point of view and generally spoken, merge always applies a source dataset to a target dataset by behalf of selecting from source and target via a join condition. No matter where the dataset comes from or goes to, a table or a view or whatever, one should quite simply always think of a source and a target selection in action. The basic pseudo-code statement may so far just look like so, executing the matched or not matched blocks when the target-source-join-conditions become true or false.

merge into __target__ using ( __source__ )
on ( __target-source-join-conditions__ )
when matched then
  update set col = val, ...
  delete where __target-delete-conditions__
when not matched then
  insert (col, ...) values (val, ...)
;