MERGE statement

This problem implies executing of two different statements (INSERT and UPDATE) against one table (Ships) in accordance with presence/absence of related rows in another table (Outcomes).

SQL Standard suggests the MERGE statement for solving similar problems. Let’s consider the usage of this statement when solving the above problem as an example.

First we’ll write the query which will return lead ships from Outcomes table, i.e. ships which have names that coincide with the class names.

select ship, ship class from Outcomes O join Classes C on C.class=O.ship;

Let’s now add the joining with Battles table and the grouping for getting minimal battle year of the each lead ship:

SELECT year(min(date)) min_year, ship, ship class
from outcomes O join battles B on O.battle= B.name
join Classes C on C.class=O.ship group by ship;

The source data is ready. Now we can pass to the writing of MERGE statement.

MERGE Ships AS target  -- table to be modified
USING (SELECT year(min(date)), ship, ship
       from outcomes O join battles B on O.battle= B.name
               join Classes C on C.class=O.ship group by ship
      ) AS source (min_year,ship, class)  -- data source above mentioned
ON (target.name = source.ship)  -- relation condition between target table and source table
WHEN MATCHED AND target.launched IS NULL -- if lead ship is in Ships table
           -- with unknown launched year
    THEN UPDATE SET target.launched = source.min_year -- updating
WHEN NOT MATCHED  -- if lead ship is absent from Ships table
    THEN INSERT VALUES(source.ship, source.class, source.min_year) -- inserting
OUTPUT $action, inserted.*, deleted.*; -- we can output modified rows

OUTPUT clause gives us opportunity to output modified rows. Inserted and Deleted tables automatically created have the same sense they has when being used in triggers, i.e. Inserted table includes rows which are added to modified table whereas Deleted table includes rows deleted from modified table.

Because our query does not delete anything, corresponding columns contain NULLs. Value in $action column is name of operator executed. Query above do the insert only as Tennessee ship is in Ships table with known launched year:

select * from Ships where name='Tennessee';

The MERGE statement can have at most two WHEN MATCHED clauses.

If two clauses are specified, the first clause must has additional condition (as in our case - AND target.launched IS NULL). The second WHEN MATCHED clause is applied for any row only when the first one is not applied.

If there are two WHEN MATCHED clauses, one must use UPDATE statement, whereas another must use DELETE statement. I.e. if we’ll add the following clause in the query

WHEN MATCHED THEN DELETE

the Tennessee ship will be deleted:

MERGE statement can’t update the same row more than once, also update and delete the same row.

WHEN NOT MATCHED [BY TARGET] THEN INSERT clause is used for inserting every row from source that does not match a row in the target table  in accordance to relation condition. In our example, such a row is that related with Bismarck ship. The MERGE statement can have only one WHEN NOT MATCHED clause.

Finally MERGE statement can include WHEN NOT MATCHED BY SOURCE THEN clause.

This affects the rows of table being modified which do not match the source table. For example, to delete lead ships that do not participate in battles from Ships table, the following clause could be used.

WHEN NOT MATCHED BY SOURCE AND target.name=target.class THEN DELETE

The result:

This clause can be used to delete or update rows. The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. If two clauses are specified, the first clause must have additional search condition (as in our example). For any given row, the second clause is only applied if the first clause is not applied. Also, in the case of two WHEN NOT MATCHED BY SOURCE clauses, one must specify UPDATE, whereas another — DELETE.