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.

•Constraints modify the records the new values statements are retrieving.

An example is given of both situations.

Example 1:

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:

In order to solve this, the user issues the following SQL statement:

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:

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:

Example 2:

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)

Suppose, these initial tours and related scheduled tours exist:

In SQL Command, the following SQL statement is executed:

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:

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