Oracle MERGE Statement (Upsert) 🔀¶
Prerequisites: INSERT, UPDATE Statements
Mentor's Note: MERGE is one of the most powerful DML commands. It means: "If the record exists, UPDATE it. If not, INSERT it." It's commonly known as an Upsert. 💡
🌟 The Scenario: The Daily Price Update 💰¶
You have a PRODUCTS table and a NEW_PRICES table from a supplier.
- If a product already exists, you want to update its price.
- If it's a brand new product, you want to add it.
- The Solution: Use MERGE to sync the two tables.
💻 1. The Basic Syntax¶
MERGE INTO target_table T
USING source_table S
ON (T.id = S.id)
WHEN MATCHED THEN
UPDATE SET T.value = S.value
WHEN NOT MATCHED THEN
INSERT (id, value) VALUES (S.id, S.value);
💻 2. Real-World Example¶
MERGE INTO products P
USING (SELECT 101 id, 500 price FROM dual) S
ON (P.product_id = S.id)
WHEN MATCHED THEN
UPDATE SET P.current_price = S.price
WHEN NOT MATCHED THEN
INSERT (product_id, current_price) VALUES (S.id, S.price);
🏗️ Architect's Note: Consistency 🛡️¶
MERGE ensures that your logic is consistent and avoids the "Race Condition" where two separate commands (Update then Insert) might fail if data changes in between.
- The Architect's Secret: Oracle makes the MERGE operation atomic. It is highly optimized for Data Warehousing where millions of rows are synced nightly.