loading..
Ðóññêèé    English
23:21

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.

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 DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
today
https://exchangesumo.com/obmen/TKS-WMU/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.