loading..
   English
08:45

MERGE statement

Do the following. If the lead ship in the Outcomes table is absent from Ships table, add it to Ships table when considering the ship name as a class name and launched year as minimal battle year for this ship. If this lead ship is in Ships table but the launched year is unknown, set the launched year as minimal battle year for this ship.

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.

Console
Execute
  1. SELECT ship, ship class FROM Outcomes O JOIN Classes C ON C.class=O.ship;

ship class
Bismarck Bismarck
Tennessee Tennessee

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

Console
Execute
  1. SELECT year(MIN(date)) min_year, ship, ship class
  2. FROM outcomes O JOIN battles B ON O.battle= B.name
  3. JOIN Classes C ON C.class=O.ship GROUP BY ship;

min_year ship class
1941 Bismarck Bismarck
1944 Tennessee Tennessee

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

  1. MERGE Ships AS target -- table to be modified
  2. USING (SELECT year(MIN(date)), ship, ship
  3. FROM outcomes O JOIN battles B ON O.battle= B.name
  4. JOIN Classes C ON C.class=O.ship GROUP BY ship
  5. ) AS source (min_year,ship, class) -- data source above mentioned
  6. ON (target.name = source.ship) -- relation condition between target table and source table
  7. WHEN MATCHED AND target.launched IS NULL -- if lead ship is in Ships table
  8. -- with unknown launched year
  9. THEN UPDATE SET target.launched = source.min_year -- updating
  10. WHEN NOT MATCHED -- if lead ship is absent from Ships table
  11. THEN INSERT VALUES(source.ship, source.class, source.min_year) -- inserting
  12. OUTPUT $action, inserted.*, deleted.*; -- we can output modified rows

$action name class launched name class launched
INSERT Bismarck Bismarck 1941 NULL NULL NULL

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:

Console
Execute
  1. SELECT * FROM Ships WHERE name='Tennessee';

name class launched
Tennessee Tennessee 1920

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

  1. WHEN MATCHED THEN DELETE
the Tennessee ship will be deleted:

$action name class launched name class launched
INSERT Bismarck Bismarck 1941 NULL NULL NULL
DELETE NULL NULL NULL Tennessee Tennessee 1920

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. 

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

The result:

$action name class launched name class launched
DELETE NULL NULL NULL Iowa Iowa 1943
DELETE NULL NULL NULL Kongo Kongo 1913
DELETE NULL NULL NULL North Carolina North Carolina 1941
DELETE NULL NULL NULL Renown Renown 1916
DELETE NULL NULL NULL Revenge Revenge 1916
DELETE NULL NULL NULL Yamato Yamato 1941
INSERT Bismarck Bismarck 1941 NULL NULL NULL

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.

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100