The MERGE Statement

Contact Us or call 1-877-932-8228
The MERGE Statement

The MERGE Statement

The MERGE statement can be used to combine several DML operations into a single statement.

Code Sample:

SQL-Data-Manipulation-Language/Demos/merge.sql
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.

Next