Skip to content

Oracle MERGE Statement (Upsert) 🔀

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.

📈 Learning Path