Common table expressions (CTE) page 2
I propose step by step build a solution and present the results in the form of select queries, so you can control the results, executing these queries online. Since the operators of data modification so far are prohibited on the site, I will give a final decision at end.
Let's start with the table you will need to update:
Since the CTE play the role of views, they can in principle be used to update the data. The words «in principle» mean that the CTE is updated, if certain conditions are met, similar to the conditions of view updates. In particular, the definition should be used only one table without grouping and calculated columns. Note that the necessary conditions in this case are satisfied.
Now we need information on flight 7772 in order to find the closest to it a suitable flight. Let's add one more CTE in the definition:
Note that in one query can define any number of common table expressions. And most importantly, CTE may include links to other CTE, than we actually now use (note the link Trip_7772 in definition of Trip_candidates)
Trip_candidates - a table expression, which determines candidates for replacement, namely, flights, departing later than 7772, and are committed between the same cities. I use string concatenation town_from + town_to, not to write separate criteria for the point of departure and destination.
Now find among the candidates rows closest to the time the flight:
Now we can replace SELECT statement by UPDATE, to solve the task:
Here we assume that between the two cities are no flights that would go in one and the same time in one direction. Otherwise, it would need an additional criterion for the selection of a single flight, as our goal - update data, not presentation of all possible candidates for replacement.
Using the CTE (common table expression) allows to determine a table in framework of a query for multiple referencings.CTE with a DELETE, you can learn by the example of removing duplicate rows from a table.