title: Merge table in Oracle with delete condition refering to source table
body: <p>For the following question it is said that the answer should be C. But I think the correct answer is Answer D as NOT MATCHED block inserts all unmatching records to target table. Can anybody explain this?
  Thank you.</p>
  
  <p>Q)View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.</p>
  
  <p><img src="https://i.stack.imgur.com/navPb.jpg" alt="enter image description here"></p>
  
  <p>Evaluate the following MERGE statement:</p>
  
  <pre><code>MERGE INTO orders_master o
  USING monthly_orders m
  ON (o.order_id = m.order_id)
  WHEN MATCHED THEN
  UPDATE SET o.order_total = m.order_total
  DELETE WHERE (m.order_total IS NULL)
  WHEN NOT MATCHED THEN
  INSERT VALUES (m.order_id, m.order_total);
  </code></pre>
  
  <p>What would be the outcome of the above statement?</p>
  
  <p>A. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.</p>
  
  <p>B. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 3.</p>
  
  <p>C. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4.</p>
  
  <p>D. The ORDERS_MASTER table would contain the ORDER IDs 1,2,3 and 4.</p>
  
  <p>Answer: C</p>