Merging Data (Oracle, DB2)
Oracle and DB2 support the use of the MERGE statement for updating or inserting rows, depending on whether they already exist in the target table. The basic syntax is:
MERGE INTOtable alias
USINGdatasource
ON (exists_test
) WHEN MATCHED THEN UPDATE SETcolumn
=value
,column
=value
, . . . WHEN NOT MATCHED THEN INSERT (column
,column
, . . . ) VALUES (value
,value
, . . . )datasource
::= {table
|view
|(subquery
)}
For example, to merge potentially new waterfall data into the upfall
table, specify:
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.
You can 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 = ...
Get SQL Pocket Guide, 2nd 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.