Oracle MERGE with DELETE

Oracle MERGE with DELETE is a SQL statement in the Oracle Specific category. Oracle extends MERGE with DELETE in the UPDATE clause. After updating, rows matching the DELETE condition are removed. The syntax is MERGE INTO t USING s ON () WHEN MATCHED THEN UPDATE ... DELETE WHERE condition;. It returns merge with delete. 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 DELETE WHERE np.discontinued = 'Y' WHEN NOT MATCHED THEN INSERT (id, name, price) VALUES (np.id, np.name, np.price); -- Updates products, deletes those marked discontinued -- Three operations in one statement! -- After UPDATE, if discontinued=Y, the row is deleted -- If no UPDATE matched, DELETE is not evaluated 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).