MERGE INTO (Oracle)

MERGE INTO (Oracle) is a SQL statement in the Oracle Specific category. Oracle: atomic upsert — update if exists, insert if not. Also supports DELETE in WHEN MATCHED. The syntax is MERGE INTO target t USING source s ON (condition) WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT;. It returns rows merged. A typical example: MERGE INTO products p USING new_products np ON (p.id = np.id) WHEN MATCHED THEN UPDATE SET p.price = np.price, p.updated_at = SYSDATE WHEN NOT MATCHED THEN INSERT (id, name, price) VALUES (np.id, np.name, np.price); -- Oracle extension: DELETE condition WHEN MATCHED THEN UPDATE SET p.price = np.price DELETE WHERE np.discontinued = 'Y'; A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c).