The first step is to create the FLIGHTS table that will be used in this tutorial. This table contains details about scheduled flights including the source and destination city, flight departure time, flight duration, and operating carrier code. It is a range-partitioned table that is partitioned based on the airport code of the city from which the flights depart.
create table flights (
flight_id integer not null primary key,
flight_number varchar2(6) not null,
departure_airport_code varchar2(3) not null,
destination_airport_code varchar2(3) not null,
departure_datetime timestamp with time zone not null,
flight_duration interval day to second(0) not null,
operating_carrier_code varchar2(2) not null
) partition by range (departure_airport_code) (
partition pA_E values less than ('F'),
partition pF_J values less than ('K'),
partition pK_O values less than ('P'),
partition pP_T values less than ('U'),
partition pU_Z values less than ('a')
);
Insert data into the FLIGHTS table. The following statements insert seven rows into the FLIGHTS table and commit the changes.
insert into flights
values (1, 'BA123', 'LHR', 'JFK', timestamp'2015-01-01 10:00:00 -00:00', interval '5' hour, 'BA');
insert into flights
values (2, 'BA123', 'LHR', 'JFK', timestamp'2015-01-02 10:00:00 -00:00', interval '5' hour, 'BA');
insert into flights
values (3, 'AA567', 'LHR', 'JFK', timestamp'2015-01-01 13:00:00 -00:00', interval '6' hour, 'AA');
insert into flights
values (4, 'BA124', 'JFK', 'LHR', timestamp'2015-01-01 22:00:00 -05:00', interval '5' hour, 'BA');
insert into flights
values (5, 'BA124', 'JFK', 'LHR', timestamp'2015-01-02 22:00:00 -05:00', interval '5' hour, 'BA');
insert into flights
values (6, 'AA567', 'JFK', 'LHR', timestamp'2015-01-01 20:00:00 -05:00', interval '6' hour, 'AA');
insert into flights
values (7, 'AA987', 'JFK', 'SFO', timestamp'2015-01-02 12:00:00 -05:00', interval '4' hour, 'AA');
commit;
This statement updates the value of a single column, FLIGHT_NUMBER, in a particular row. The row to be updated is specified using a WHERE clause.
After the update operation, a SELECT statement displays the data in the FLIGHTS table. Verify that the specified column was updated.
update flights set flight_number = 'AA986' where flight_id = 7;
select * from flights;
This statement updates a single column, FLIGHT_DURATION, in the FLIGHTS table by setting its value as a factor of its existing value.
The SELECT statement that follows the update operation displays the data in the FLIGHTS table, thus verifying that the update operation completed successfully.
update flights
set flight_duration = flight_duration + interval '30' minute
where flight_id = 7;
select * from flights;
The following statement updates three columns, FLIGHT_NUMBER, FLIGHT_DURATION, and OPERATING_CARRIER_CODE, in a single row of the FLIGHTS table. The row to be updated is identified using the WEHERE clause.
A SELECT statement displays the updated content for the FLIGHTS table for verification.
update flights
set flight_number = 'BA986', flight_duration = interval '5' hour, operating_carrier_code = 'BA'
where flight_id = 7;
select * from flights;
This statement uses a correlated subquery to update a single column across multiple rows in the FLIGHTS table. It increases the duration of all BA flights by thirty minutes.
The subquery returns all flights with OPERATING_CARRIER_CODE set to 'BA'. This result is used as the predicate value in the UPDATE statement. Thus, the value of the FLIGHT_DURATION column is updated for all rows FLIGHT_ID matches those returned by the query.
The SELECT statement displays the data in the FLIGHTS table and is used to verify that the update operation completed successfully.
update flights f1
set f1.flight_duration = f1.flight_duration + interval '30' minute
where f1.flight_id in (select f2.flight_id
from flights f2
where f2.operating_carrier_code = 'BA');
select * from flights;
This statement performs a correlated update of the rows in the FLIGHTS table. For all flights with OPERATING_CARRIER_CODE='BA' that depart on 1 Jan 2015 , it appends zero to the flight number and sets the flight duration to the duration of the AA flight on the same day and same route. It also sets the departure times for these flights to 11AM GMT.
The SELECT statement displays the updated contents of the FLIGHTS table and enables you to verify that the data has been successfully updated.
update flights f1
set f1.departure_datetime = timestamp'2015-01-01 11:00:00 -00:00',
(f1.flight_number, f1.flight_duration) = (
select f2.flight_number || '0', f2.flight_duration
from flights f2
where f2.departure_airport_code = f1.departure_airport_code
and f2.destination_airport_code = f1.destination_airport_code
and trunc(f2.departure_datetime) = trunc(f2.departure_datetime)
and f2.operating_carrier_code = 'AA'
)
where f1.operating_carrier_code = 'BA'
and trunc(f1.departure_datetime) = date'2015-01-01';
select * from flights;