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:
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
|
|
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:
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
|
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:
TOUR
|
Destination
|
Tour_type
|
default_discount
|
PROVENCE
|
SURVIVAL
|
10
|
SCHEDTOUR
|
Id
|
Discount
|
1
|
0
|
2
|
0
|
3
|
0
|
4
|
0
|
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:
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
|
|