Oracle/Merge

The Oracle MERGE statement may be used for various purposes. Its main function is to merge data from one table (or view) into another table. Inserts and Updates can be performed, Deletes are available since Oracle 10g.

Merge new and updated client data from table client_new into table client:

MERGE INTO clients c
     USING (SELECT id, name, address_id FROM clients_new) cn
        ON (cn.id = c.id)
  WHEN MATCHED THEN -- update existing
    UPDATE SET c.name = cn.name, e.address_id = cn.address_id
  WHEN NOT MATCHED THEN -- create missing (new)
    INSERT INTO (cn.id, cn.name, cn.address_id)
      VALUES (c.id, c.name, c.address_id); -- we assume that there will be no id conflicts!

An optional Delete clause will be executed if the Delete's WENN clause and the Merge's ON clause matche:

MERGE INTO clients c
     USING (SELECT id, name, address_id FROM clients_update) cu
        ON (cu.id = c.id)
  WHEN MATCHED THEN -- update existing
    UPDATE SET c.name = cu.name, e.address_id = cu.address_id
  DELETE WHERE (cu.deletewhenupdated = 1);

An example for Update using join and merge may be found here.