The MERGE statement can be used to combine several DML operations into a single statement.
SELECT * FROM regions; CREATE TABLE new_regions AS SELECT 3 AS id , 'ASIA' AS name FROM dual; INSERT INTO new_regions(id, name) VALUES(9, 'MARS'); MERGE INTO regions r USING ( SELECT * FROM new_regions ) n ON (r.REGION_ID = n.id) WHEN MATCHED THEN UPDATE SET r.region_name = n.name WHEN NOT MATCHED THEN INSERT (r.region_id, r.region_name) VALUES (n.id, n.name); SELECT * FROM regions; DELETE FROM regions WHERE region_id = 9; UPDATE regions SET region_name=initcap(region_name) WHERE region_name='ASIA'; DROP TABLE new_regions; SELECT * FROM regions;
Start by examining the contents of the regions table. A new table is created called new regions and an additional record is inserted into it. The merge statement is used to merge the data in the new_regions table into the regions table. If a match is found, the region name is updated. If no match is found, a new record was inserted. The remaining statements reset the schema to its original state.