SQL Pocket Guide, 3rd Edition

Merging Data

DB2, Oracle, and SQL Server 2008 support the use of the MERGE statement for updating or inserting rows, depending on whether they already exist in the target table. For example, to merge potentially new waterfall data into the upfall table, specify the following:

MERGE INTO upfall u
USING (SELECT * FROM new_falls) nf
   ON (u.id = nf.id)
WHEN MATCHED THEN UPDATE
   SET u.name = nf.name,
       u.open_to_public = nf.open_to_public
WHEN NOT MATCHED THEN INSERT
   (id, name, datum, zone, northing, easting,
    lat_lon, county_id, open_to_public,
    owner_id, description, confirmed_date)
   VALUES (nf.id, nf.name, nf.datum, nf.zone,
      nf.northing, nf.easting, nf.lat_lon,
      nf.county_id, nf.open_to_public,
      nf.owner_id, nf.description,
      nf.confirmed_date);

This statement updates only name and open_to_public for existing waterfalls, although you could choose to update all columns if you wanted to do so. For new falls, all columns are inserted into the upfall table.

Oracle allows you to place WHERE conditions on both the UPDATE and INSERT operations. In addition, Oracle allows you to specify rows to be deleted following an UPDATE operation:

MERGE INTO upfall u USING (SELECT * FROM new_falls) nf ON (u.id = nf.id) WHEN MATCHED THEN UPDATE SET u.name = nf.name, u.open_to_public = nf.open_to_public WHERE nf.name IS NOT NULL DELETE WHERE u.open_to_public = 'n' WHEN NOT MATCHED THEN INSERT (id, name, datum, zone, northing, easting, lat_lon, county_id, open_to_public, owner_id, description, confirmed_date) VALUES (nf.id, ...