Oracle MERGE Statement

It was introduced in Oracle 9i, to insert or update the data in a table conditionally. Its also called upsert.

It is faster as it reduces table scans.

Found this example where data from the hr table is merged with the employees table. Here we have MERGE INTO statement, instead of the usual INSERT INTO. And the table from which the new data is to be picked, is specified using USING keyword.

The next step is to specify the merge condition, based on which the data will be checked. 

When the condition will match, an update will be fired to update the employees table, otherwise if not matched, the data will be inserted into employees table.

MERGE INTO employees e
    USING hr h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)

    VALUES (h.emp_id, h.address);


MERGE statement enhancements in 10g
There are few additions done to make merge statement more flexible.
- Now, we can skip one of the MATCHED or NOT MATCHED blocks.
- a WHERE condition can also be added to the insert/update part.
- a DELETE statement can also be added to the insert/update part but only on the MATCHED block.