With merge you can insert, update or both
I want to import changes from one table in another table.
To insert the missing row from T2 into T1, I could need the following subquery with INSERT.
insert into t1 select * from t2 where not exists ( select * from t1 where t1.id=t2.id);
The same code would be done in Merge with
merge into t1 using t2 on (t1.id=t2.id) when not matched then insert values(t2.id,t2.name);
And what about updating the T1 table with the T2 values
update t1 set (name)=(select name from t2 where id=t1.id) where id in (select id from t2);
with merge :
merge into t1 using t2 on (t1.id=t2.id) when matched then update set name=t2.name;
Quite nice, is not it! I find this syntax very intuitive.
Apart from syntax looking, Merge can also combine insert and update in once, which make it more stable (only one statement) and more performant (table t2 is read only once).
merge into t1 using t2 on (t1.id=t2.id) when not matched then insert values(t2.id,t2.name) when matched then update set name=t2.name;