loading..
   English
19:55

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:

Console
Execute
  1. ;WITH Trip_for_replace AS(
  2. SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' )
  3. SELECT * FROM Trip_for_replace;

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
  1. ;WITH Trip_for_replace AS(
  2. SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ),
  3. Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 )
  4. SELECT * FROM Trip_7772;

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)
Console
Execute
  1. ;WITH Trip_for_replace AS(
  2. SELECT * FROM Pass_in_trip
  3. WHERE trip_no=7772 AND date='20051129' ), Trip_7772 AS (
  4. SELECT * FROM Trip WHERE trip_no=7772 ),
  5. Trip_candidates AS(
  6. SELECT Trip.* FROM Trip, Trip_7772
  7. WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from +
  8. Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out )
  9. SELECT * FROM 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:

Console
Execute
  1. ;WITH Trip_for_replace AS(
  2. SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ),
  3. Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ),
  4. Trip_candidates AS( SELECT Trip.* FROM Trip, Trip_7772
  5. WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from +
  6. Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out ),
  7. Trip_replace AS( SELECT * FROM Trip_candidates
  8. WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) )
  9. SELECT * FROM Trip_replace;

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

  1. WITH Trip_for_replace AS(
  2. SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ),
  3. Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ),
  4. Trip_candidates AS( SELECT Trip.* FROM Trip, Trip_7772
  5. WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from +
  6. Trip_7772.town_to AND Trip.time_out > Trip_7772.time_out ),
  7. Trip_replace AS( SELECT * FROM Trip_candidates
  8. WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) )
  9. 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.

Bookmark and Share
Pages 1 2 3

Content:

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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100