MERGE

The MERGE statement allows developers to use one command to perform deterministic inserts, updates, and deletes on a table based on a source table.

When synchronizing some information across two tables, up to three operations have to be performed. First, any new rows need to be inserted into the target table. Then existing rows have to be updated. Finally, old rows no longer in use may need to be deleted. This can lead to a lot of repetitive logic which needs to be maintained, and in turn can lead to subtle bugs.

merge [target] t
using [source] s on t.id = s.id
when matched then update t.name = s.name, t.age = s.age -- use "rowset1"
when not matched then insert values(id,name,age) -- use "rowset2"
when source not matched then delete; -- use "rowset3"