New sql features

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:

Console

Execute

;WITH Trip_for_replace AS( 
  SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ) 
    SELECT * FROM Trip_for_replace;

;WITH Trip_for_replace AS(   SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' )     SELECT * FROM Trip_for_replace;

    Scheme

No scheme

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:

Console

Execute

;WITH Trip_for_replace AS( 
  SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ), 
  Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ) 
  SELECT * FROM Trip_7772;

;WITH Trip_for_replace AS(   SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ),   Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 )   SELECT * FROM Trip_7772;

    Scheme

No scheme

Console

Execute

;WITH Trip_for_replace AS( 
  SELECT * FROM Pass_in_trip 
  WHERE trip_no=7772 AND date='20051129' ), Trip_7772 AS ( 
        SELECT * FROM Trip WHERE trip_no=7772 ), 
  Trip_candidates AS( 
  SELECT Trip.* FROM Trip, Trip_7772 
  WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from + 
    Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out ) 
SELECT * FROM Trip_candidates;

;WITH Trip_for_replace AS(   SELECT * FROM Pass_in_trip   WHERE trip_no=7772 AND date='20051129' ), Trip_7772 AS (         SELECT * FROM Trip WHERE trip_no=7772 ),   Trip_candidates as(   SELECT Trip.* FROM Trip, Trip_7772   WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from +     Trip_7772.town_to and Trip.time_out > Trip_7772.time_out ) SELECT * FROM Trip_candidates;

    Scheme

No scheme

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:

Console

Execute

;WITH Trip_for_replace AS( 
  SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ), 
  Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ), 
  Trip_candidates AS( SELECT Trip.* FROM Trip, Trip_7772 
   WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from + 
       Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out ), 
  Trip_replace AS( SELECT * FROM Trip_candidates 
           WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) ) 
  SELECT * FROM Trip_replace;

;WITH Trip_for_replace AS(   SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ),   Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ),   Trip_candidates AS( SELECT Trip.* FROM Trip, Trip_7772    WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from +        Trip_7772.town_to and Trip.time_out > Trip_7772.time_out ),   Trip_replace AS( SELECT * FROM Trip_candidates            WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) )   SELECT * FROM Trip_replace;

    Scheme

No scheme

 Now we can replace SELECT statement by UPDATE, to solve the task:

WITH Trip_for_replace AS( 
  SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ), 
  Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ), 
  Trip_candidates AS( SELECT Trip.* FROM Trip, Trip_7772 
    WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from + 
         Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out ), 
    Trip_replace AS( SELECT * FROM Trip_candidates 
           WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) ) 
  UPDATE Trip_for_replace SET trip_no = (SELECT trip_no FROM Trip_replace);

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.

Content:

Recursive CTE