1Z0-047 · Question #214
View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables. Evaluate the following MERGE statement: MERGE INTO orders_master o USING monthly_orders m ON (o.order_id =…
The correct answer is C. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4. See the full explanation below for the reasoning.
Question
View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables. Evaluate the following MERGE statement:
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); What would be the outcome of the above statement?
Exhibit
Options
- AThe ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.
- BThe ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 3.
- CThe ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4.
- DThe ORDERS_MASTER table would contain the ORDER IDs 1,2,3 and 4.
How the community answered
(30 responses)- A7% (2)
- B13% (4)
- C77% (23)
- D3% (1)
Community Discussion
3
Brenda K.Apr 26, 2026C is right, the DELETE fires only on matched rows where order_total IS NULL, so order_id 3 gets dropped but 4 inserts clean.
Toby R.Apr 16, 2026This one tripped me up at first because I almost forgot the DELETE in a MERGE clause only fires on rows that were just matched and updated, not on source rows that failed to match. The way it works: rows in ORDERS_MASTER that match on order_id get their order_total updated, then any of those updated rows where the source order_total IS NULL get deleted. Order ID 3 is in MONTHLY_ORDERS but not in ORDERS_MASTER, so it hits the NOT MATCHED branch and gets inserted. Order ID 4 is in MONTHLY_ORDERS with a NULL order_total, so it also hits NOT MATCHED and gets inserted, and the DELETE condition does not apply there because DELETE only runs against MATCHED rows. Whatever order IDs had a NULL order_total on the source side and were already in ORDERS_MASTER would get wiped out by the delete, leaving you with 1, 2, and 4. On my actual exam I stared at this for probably two minutes, drew a little two-column table on my scratch board and traced each row through the three paths, matched-update, matched-delete, and not-matched-insert. The moment I realized the DELETE clause cannot touch rows coming through the NOT MATCHED path, C was obviously right. Write that down somewhere because the exam definitely tries to make you think the NULL order_total on a not-matched row should trigger the delete.
Wesley A.May 16, 2026Kept picking B until I realized the DELETE fires after the UPDATE, killing order 3.