title: When doing a MERGE in Oracle SQL, how can I update rows that aren't matched in the SOURCE?
body: <p>I have a <code>main</code> database and a <code>report</code> database, and I need to sync a table from <code>main</code> into <code>report</code>.</p>
  
  <p>However, when an item gets deleted in the <code>main</code> database, I only want to set an <code>IsDeleted</code> flag in the <code>report</code> database.  </p>
  
  <p>What is an elegant way to do this?</p>
  
  <p>I'm currently using a MERGE statement, like this:</p>
  
  <pre><code>MERGE INTO report.TEST target
  USING (SELECT * FROM main.TEST) source
     ON (target.ID = source.ID)
  WHEN MATCHED THEN
      UPDATE SET (target... = source...)
  WHEN NOT MATCHED THEN
      INSERT (...) VALUES (source...)
  ;
  </code></pre>
  
  <p>The <code>WHEN NOT MATCHED</code> statement gives me all NEW values from <code>main</code>, but I also want to update all OLD values from <code>report</code>.</p>
  
  <p>I'm using Oracle PL/SQL.</p>