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.
Let's now add the joining with Battles table and the grouping for getting minimal battle year of the each lead ship:
The source data is ready. Now we can pass to the writing of MERGE statement.
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:
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
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.
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.