Joe Celko's SQL for Smarties, 3rd Edition

8.5. MERGE Statement

SQL-99 added a single statement to mimic a common magnetic tape file system “merge and insert” procedure. The business logic, in a pseudocode, is like this.

FOR EACH row IN the Transactions table
DO IF working row NOT IN Master table
   THEN INSERT working row INTO the Master table;
   ELSE UPDATE Master table
           SET Master table columns to the Transactions table
values
         WHERE they meet a matching criteria;
   END IF;
END FOR;

In the 1950s, we would sort the transaction tape(s) and Master tape on the same key, read each one looking for a match, then perform whatever logic is needed. In its simplest form, the MERGE statement looks like this:

MERGE INTO <table name> [AS [<correlation name>]] USING <table reference> ON <search condition> ...