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, ...
Get SQL Pocket Guide, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.