If I asked you to come up with all the different kinds of CRUD query statements you could think of, what statements would come to mind?

INSERT, SELECT, UPDATE, DELETE are easy to come by.

What about the less common MERGE statement?

It’s used when you’d like to update one table from another “data source”.

A data source could be almost anything, from VALUES statements, to subqueries, to other tables.

It provides an easy to way to join upon two tables, and decide whether to UPDATE, INSERT or DELETE on each joined record.

Here’s an example demonstrating all three different kinds of statements within a single MERGE statement:

MERGE INTO stocks s
USING stock_changes sc
ON s.name = sc.name
WHEN NOT MATCHED AND sc.stock_delta > 0 THEN
  INSERT VALUES(s.name, s.stock_delta)
WHEN MATCHED AND s.stock + sc.stock_delta > 0 THEN
  UPDATE SET stock = s.stock + sc.stock_delta
WHEN MATCHED THEN
  DELETE;

Notice how easy it is to combine all four different kind of mutation statements into a single query.

When migrating to different database schemas, it’s more common to see this kind of join-then-mutation pattern.

The merge statement can make migrations simpler and easier to maintain, so be sure to use them when given the chance.