Change in Functionality With Query Combining

With the query combining mechanism, the result of the OLD_NEWVALUES statement may differ from the result of the separate SELECT_FOR_UPDATE and NEWVALUES statements. This change in functionality occurs when:

•The new values select statement addresses the manipulated table a second time.

In the Travel Agency, for each day of a scheduled tour, a record with a description of the activities of the day is stored in the DAY table. Suppose a mistake has been made with the day descriptions of a particular scheduled tour. The activities of the second day are stored with the first day, and so on:

Schedtour_id	Day_date	Description
1	01-03-2000	Activities day two
1	02-03-2000	Activities day three
1	03-03-2000	Activities day four
1	04-03-2000

UPDATE    day d1
SET       description =
(    SELECT    d2.description
     FROM      day d2
     WHERE     d2.day_date = d1.day_date - 1

)
WHERE schedtour_id = 1
AND day_date > '01-03-2000'

If separate SELECT_FOR_UPDATE and NEWVALUES statements were executed, this SQL statement would not have the required effect:

Schedtour_id	Day_date	Description
1	01-03-2000	Activities day two
1	02-03-2000	Activities day two
1	03-03-2000	Activities day two
1	04-03-2000	Activities day two

This is because after the update of the description of day '02-03-2000' to 'Activities day two', the new values for day '03-03-2000' are determined.

However, if only one OLD_NEWVALUES statement is issued, the result will be as expected. The reason for this being that the combined OLD_NEWVALUES statement is executed once and when it fetches a record it will not notice the updates that are occurring along the way:

Schedtour_id	Day_date	Description
1	01-03-2000	Activities day two
1	02-03-2000	Activities day two
1	03-03-2000	Activities day three
1	04-03-2000	Activities day four

In the Travel Agency, suppose tours have a default discount percentage and scheduled tours have a discount percentage, depending on the season in which the scheduled tour takes place. A business rule, "A tour has no default discount if there are more than 2 related scheduled tours with a discount", is implemented by the constraint:

UPDATE    tour t
SET       t.default_discount = 0
WHERE     2 <
(

       SELECT   COUNT(*)
       FROM     schedtour st
       WHERE    t.destination = st.destination
       AND      t.tour_type = st.tour_type
       AND      st.discount > 0

)

TOUR
Destination		Tour_type	default_discount
PROVENCE	SURVIVAL	10

SCHEDTOUR
Id	Discount
1	0
2	0
3	0
4	0

UPDATE SCHEDTOUR st
SET st.discount =
(

       SELECT     t.default_discount
       FROM       tour t
       WHERE      t.destination = st.destination
       AND        t.tour_type = st.tour_type

)

If the SELECT_FOR_UPDATE statement were not combined with the NEWVALUES statement, this statement would not have the required effect. The reason for this being that the moment the new values are read for the last scheduled tour, the constraint has already updated the default discount value to 0:

TOUR
Destination		Tour_type	default_discount
PROVENCE	SURVIVAL	0

SCHEDTOUR
Id	Discount
1	10
2	10
3	10
4	0

However, if one OLD_NEWVALUES statement is issued, the result will be as expected: The combined OLD_NEWVALUES statement is executed once:

TOUR
Destination		Tour_type	default_discount
PROVENCE	SURVIVAL	0

SCHEDTOUR
Id	Discount
1	10
2	10
3	10
4	10