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 t
     using dual
        on (dummy=x)
  when matched then
    update set y = nvl(y, 0)+1
    delete where y = 2
  when not matched then
    insert (x,y)
      values (dummy, null);

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