Oracle Live SQL - Script: UPDATE statement examples

Statement 1

REM Basic table storing flight details

Basic table storing flight details

Statement 2

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') 
)

Table created.

Statement 3

REM Sample data

Sample data

Statement 4

insert into flights  
values (1, 'BA123', 'LHR', 'JFK', timestamp'2015-01-01 10:00:00 -00:00', interval '5' hour, 'BA')

1 row(s) inserted.

Statement 5

insert into flights  
values (2, 'BA123', 'LHR', 'JFK', timestamp'2015-01-02 10:00:00 -00:00', interval '5' hour, 'BA')

1 row(s) inserted.

Statement 6

insert into flights  
values (3, 'AA567', 'LHR', 'JFK', timestamp'2015-01-01 13:00:00 -00:00', interval '6' hour, 'AA')

1 row(s) inserted.

Statement 7

insert into flights  
values (4, 'BA124', 'JFK', 'LHR', timestamp'2015-01-01 22:00:00 -05:00', interval '5' hour, 'BA')

1 row(s) inserted.

Statement 8

insert into flights  
values (5, 'BA124', 'JFK', 'LHR', timestamp'2015-01-02 22:00:00 -05:00', interval '5' hour, 'BA')

1 row(s) inserted.

Statement 9

insert into flights  
values (6, 'AA567', 'JFK', 'LHR', timestamp'2015-01-01 20:00:00 -05:00', interval '6' hour, 'AA')

1 row(s) inserted.

Statement 10

insert into flights  
values (7, 'AA987', 'JFK', 'SFO', timestamp'2015-01-02 12:00:00 -05:00', interval '4' hour, 'AA')

1 row(s) inserted.

Statement 11

commit

Statement processed.

Statement 12

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 BA124 JFK LHR 01-JAN-15 10.00.00.000000 PM -05:00 +00 05:00:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:00:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 AA
7 AA987 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 04:00:00 AA
1 BA123 LHR JFK 01-JAN-15 10.00.00.000000 AM +00:00 +00 05:00:00 BA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 05:00:00 BA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 06:00:00 AA

7 rows selected.

FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
4	BA124	JFK	LHR	01-JAN-15 10.00.00.000000 PM -05:00	+00 05:00:00	BA
5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000 PM -05:00	+00 05:00:00	BA
6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000 PM -05:00	+00 06:00:00	AA
7	AA987	JFK	SFO	02-JAN-15 12.00.00.000000 PM -05:00	+00 04:00:00	AA
1	BA123	LHR	JFK	01-JAN-15 10.00.00.000000 AM +00:00	+00 05:00:00	BA
2	BA123	LHR	JFK	02-JAN-15 10.00.00.000000 AM +00:00	+00 05:00:00	BA
3	AA567	LHR	JFK	01-JAN-15 01.00.00.000000 PM +00:00	+00 06:00:00	AA

Statement 13

REM Single row, single column update

Single row, single column update

Statement 14

REM Changing the flight number for flight id 7

Changing the flight number for flight id 7

Statement 15

update flights 
set    flight_number = 'AA986' 
where  flight_id = 7

1 row(s) updated.

Statement 16

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 BA124 JFK LHR 01-JAN-15 10.00.00.000000 PM -05:00 +00 05:00:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:00:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 AA
7 AA986 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 04:00:00 AA
1 BA123 LHR JFK 01-JAN-15 10.00.00.000000 AM +00:00 +00 05:00:00 BA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 05:00:00 BA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 06:00:00 AA

7 rows selected.

FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
4	BA124	JFK	LHR	01-JAN-15 10.00.00.000000 PM -05:00	+00 05:00:00	BA
5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000 PM -05:00	+00 05:00:00	BA
6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000 PM -05:00	+00 06:00:00	AA
7	AA986	JFK	SFO	02-JAN-15 12.00.00.000000 PM -05:00	+00 04:00:00	AA
1	BA123	LHR	JFK	01-JAN-15 10.00.00.000000 AM +00:00	+00 05:00:00	BA
2	BA123	LHR	JFK	02-JAN-15 10.00.00.000000 AM +00:00	+00 05:00:00	BA
3	AA567	LHR	JFK	01-JAN-15 01.00.00.000000 PM +00:00	+00 06:00:00	AA

Statement 17

REM Single row, multicolumn update

Single row, multicolumn update

Statement 18

REM Setting the flight number, duration and operating carrier for flight id 7

Setting the flight number, duration and operating carrier for flight id 7

Statement 19

update flights 
set    flight_number = 'BA986', 
       flight_duration = interval '5' hour, 
       operating_carrier_code = 'BA' 
where  flight_id = 7

1 row(s) updated.

Statement 20

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 BA124 JFK LHR 01-JAN-15 10.00.00.000000 PM -05:00 +00 05:00:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:00:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 AA
7 BA986 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 05:00:00 BA
1 BA123 LHR JFK 01-JAN-15 10.00.00.000000 AM +00:00 +00 05:00:00 BA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 05:00:00 BA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 06:00:00 AA

7 rows selected.

FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
4	BA124	JFK	LHR	01-JAN-15 10.00.00.000000 PM -05:00	+00 05:00:00	BA
5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000 PM -05:00	+00 05:00:00	BA
6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000 PM -05:00	+00 06:00:00	AA
7	BA986	JFK	SFO	02-JAN-15 12.00.00.000000 PM -05:00	+00 05:00:00	BA
1	BA123	LHR	JFK	01-JAN-15 10.00.00.000000 AM +00:00	+00 05:00:00	BA
2	BA123	LHR	JFK	02-JAN-15 10.00.00.000000 AM +00:00	+00 05:00:00	BA
3	AA567	LHR	JFK	01-JAN-15 01.00.00.000000 PM +00:00	+00 06:00:00	AA

Statement 21

REM Single row, single column self update

Single row, single column self update

Statement 22

REM Increase the time of flight_id 7 by thirty minutes

Increase the time of flight_id 7 by thirty minutes

Statement 23

update flights 
set    flight_duration = flight_duration + interval '30' minute 
where  flight_id = 7

1 row(s) updated.

Statement 24

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 BA124 JFK LHR 01-JAN-15 10.00.00.000000 PM -05:00 +00 05:00:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:00:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 AA
7 BA986 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 05:30:00 BA
1 BA123 LHR JFK 01-JAN-15 10.00.00.000000 AM +00:00 +00 05:00:00 BA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 05:00:00 BA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 06:00:00 AA

7 rows selected.

FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
4	BA124	JFK	LHR	01-JAN-15 10.00.00.000000 PM -05:00	+00 05:00:00	BA
5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000 PM -05:00	+00 05:00:00	BA
6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000 PM -05:00	+00 06:00:00	AA
7	BA986	JFK	SFO	02-JAN-15 12.00.00.000000 PM -05:00	+00 05:30:00	BA
1	BA123	LHR	JFK	01-JAN-15 10.00.00.000000 AM +00:00	+00 05:00:00	BA
2	BA123	LHR	JFK	02-JAN-15 10.00.00.000000 AM +00:00	+00 05:00:00	BA
3	AA567	LHR	JFK	01-JAN-15 01.00.00.000000 PM +00:00	+00 06:00:00	AA

Statement 25

REM Correlated subquery, multi-row update, single column self update

Correlated subquery, multi-row update, single column self update

Statement 26

REM increase the length of all BA flights by thirty minutes

increase the length of all BA flights by thirty minutes

Statement 27

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')

5 row(s) updated.

Statement 28

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 BA124 JFK LHR 01-JAN-15 10.00.00.000000 PM -05:00 +00 05:30:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:30:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 AA
7 BA986 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 06:00:00 BA
1 BA123 LHR JFK 01-JAN-15 10.00.00.000000 AM +00:00 +00 05:30:00 BA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 05:30:00 BA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 06:00:00 AA

7 rows selected.

FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
4	BA124	JFK	LHR	01-JAN-15 10.00.00.000000 PM -05:00	+00 05:30:00	BA
5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000 PM -05:00	+00 05:30:00	BA
6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000 PM -05:00	+00 06:00:00	AA
7	BA986	JFK	SFO	02-JAN-15 12.00.00.000000 PM -05:00	+00 06:00:00	BA
1	BA123	LHR	JFK	01-JAN-15 10.00.00.000000 AM +00:00	+00 05:30:00	BA
2	BA123	LHR	JFK	02-JAN-15 10.00.00.000000 AM +00:00	+00 05:30:00	BA
3	AA567	LHR	JFK	01-JAN-15 01.00.00.000000 PM +00:00	+00 06:00:00	AA

Statement 29

REM Correlated update

Correlated update

Statement 30

REM Set the flight number and duration for BA flights departing on 1 Jan 2015

Set the flight number and duration for BA flights departing on 1 Jan 2015

Statement 31

REM to be the flight number appended with zero and duration of the AA flight

to be the flight number appended with zero and duration of the AA flight

Statement 32

REM on the same day and route

on the same day and route

Statement 33

REM Also set the departure times for these flights to 11AM GMT

Also set the departure times for these flights to 11AM GMT

Statement 34

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'

2 row(s) updated.

Statement 35

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 AA5670 JFK LHR 01-JAN-15 11.00.00.000000 AM +00:00 +00 06:00:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:30:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 AA
7 BA986 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 06:00:00 BA
1 AA5670 LHR JFK 01-JAN-15 11.00.00.000000 AM +00:00 +00 06:00:00 BA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 05:30:00 BA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 06:00:00 AA

7 rows selected.

FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
4	AA5670	JFK	LHR	01-JAN-15 11.00.00.000000 AM +00:00	+00 06:00:00	BA
5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000 PM -05:00	+00 05:30:00	BA
6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000 PM -05:00	+00 06:00:00	AA
7	BA986	JFK	SFO	02-JAN-15 12.00.00.000000 PM -05:00	+00 06:00:00	BA
1	AA5670	LHR	JFK	01-JAN-15 11.00.00.000000 AM +00:00	+00 06:00:00	BA
2	BA123	LHR	JFK	02-JAN-15 10.00.00.000000 AM +00:00	+00 05:30:00	BA
3	AA567	LHR	JFK	01-JAN-15 01.00.00.000000 PM +00:00	+00 06:00:00	AA

Statement 36

REM Update all rows, no where clause

Update all rows, no where clause

Statement 37

update flights 
set    operating_carrier_code = 'BA'

7 row(s) updated.

Statement 38

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 AA5670 JFK LHR 01-JAN-15 11.00.00.000000 AM +00:00 +00 06:00:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:30:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 BA
7 BA986 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 06:00:00 BA
1 AA5670 LHR JFK 01-JAN-15 11.00.00.000000 AM +00:00 +00 06:00:00 BA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 05:30:00 BA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 06:00:00 BA

7 rows selected.

FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
4	AA5670	JFK	LHR	01-JAN-15 11.00.00.000000 AM +00:00	+00 06:00:00	BA
5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000 PM -05:00	+00 05:30:00	BA
6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000 PM -05:00	+00 06:00:00	BA
7	BA986	JFK	SFO	02-JAN-15 12.00.00.000000 PM -05:00	+00 06:00:00	BA
1	AA5670	LHR	JFK	01-JAN-15 11.00.00.000000 AM +00:00	+00 06:00:00	BA
2	BA123	LHR	JFK	02-JAN-15 10.00.00.000000 AM +00:00	+00 05:30:00	BA
3	AA567	LHR	JFK	01-JAN-15 01.00.00.000000 PM +00:00	+00 06:00:00	BA

Statement 39

REM update all rows in partition pK_O (those departing from LHR)

update all rows in partition pK_O (those departing from LHR)

Statement 40

REM to have a flight duration of 7 hours

to have a flight duration of 7 hours

Statement 41

update flights partition (pK_O) 
set    flight_duration = interval '7' hour

3 row(s) updated.

Statement 42

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 AA5670 JFK LHR 01-JAN-15 11.00.00.000000 AM +00:00 +00 06:00:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:30:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 BA
7 BA986 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 06:00:00 BA
1 AA5670 LHR JFK 01-JAN-15 11.00.00.000000 AM +00:00 +00 07:00:00 BA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 07:00:00 BA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 07:00:00 BA

7 rows selected.

FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
4	AA5670	JFK	LHR	01-JAN-15 11.00.00.000000 AM +00:00	+00 06:00:00	BA
5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000 PM -05:00	+00 05:30:00	BA
6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000 PM -05:00	+00 06:00:00	BA
7	BA986	JFK	SFO	02-JAN-15 12.00.00.000000 PM -05:00	+00 06:00:00	BA
1	AA5670	LHR	JFK	01-JAN-15 11.00.00.000000 AM +00:00	+00 07:00:00	BA
2	BA123	LHR	JFK	02-JAN-15 10.00.00.000000 AM +00:00	+00 07:00:00	BA
3	AA567	LHR	JFK	01-JAN-15 01.00.00.000000 PM +00:00	+00 07:00:00	BA

Statement 43

REM Update a query

Update a query

Statement 44

REM Set all the flights that take 7 hours to be operated by AA

Set all the flights that take 7 hours to be operated by AA

Statement 45

update (select operating_carrier_code 
        from   flights 
        where  flight_duration = interval '7' hour)  
set    operating_carrier_code = 'AA'

3 row(s) updated.

Statement 46

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 AA5670 JFK LHR 01-JAN-15 11.00.00.000000 AM +00:00 +00 06:00:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:30:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 BA
7 BA986 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 06:00:00 BA
1 AA5670 LHR JFK 01-JAN-15 11.00.00.000000 AM +00:00 +00 07:00:00 AA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 07:00:00 AA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 07:00:00 AA

7 rows selected.

FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
4	AA5670	JFK	LHR	01-JAN-15 11.00.00.000000 AM +00:00	+00 06:00:00	BA
5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000 PM -05:00	+00 05:30:00	BA
6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000 PM -05:00	+00 06:00:00	BA
7	BA986	JFK	SFO	02-JAN-15 12.00.00.000000 PM -05:00	+00 06:00:00	BA
1	AA5670	LHR	JFK	01-JAN-15 11.00.00.000000 AM +00:00	+00 07:00:00	AA
2	BA123	LHR	JFK	02-JAN-15 10.00.00.000000 AM +00:00	+00 07:00:00	AA
3	AA567	LHR	JFK	01-JAN-15 01.00.00.000000 PM +00:00	+00 07:00:00	AA

Statement 47

REM Update a query with check operation.

Update a query with check operation.

Statement 48

REM This validates that the update will not set values that do not match the where clause

This validates that the update will not set values that do not match the where clause

Statement 49

REM Select all the rows with durations of at least 4 hours, preventing

Select all the rows with durations of at least 4 hours, preventing

Statement 50

REM any rows in the table having durations less than 4 hours after the update

any rows in the table having durations less than 4 hours after the update

Statement 51

REM Throws an exception because this will set some flight durations less than 4 hours

Throws an exception because this will set some flight durations less than 4 hours

Statement 52

update (select flight_duration  
        from   flights 
        where  flight_duration >= interval '4' hour 
        with check option) 
set    flight_duration = flight_duration - interval '2' hour

ORA-01402: view WITH CHECK OPTION where-clause violation

Statement 53

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 AA5670 JFK LHR 01-JAN-15 11.00.00.000000 AM +00:00 +00 06:00:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:30:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 BA
7 BA986 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 06:00:00 BA
1 AA5670 LHR JFK 01-JAN-15 11.00.00.000000 AM +00:00 +00 07:00:00 AA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 07:00:00 AA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 07:00:00 AA

7 rows selected.

FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
4	AA5670	JFK	LHR	01-JAN-15 11.00.00.000000 AM +00:00	+00 06:00:00	BA
5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000 PM -05:00	+00 05:30:00	BA
6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000 PM -05:00	+00 06:00:00	BA
7	BA986	JFK	SFO	02-JAN-15 12.00.00.000000 PM -05:00	+00 06:00:00	BA
1	AA5670	LHR	JFK	01-JAN-15 11.00.00.000000 AM +00:00	+00 07:00:00	AA
2	BA123	LHR	JFK	02-JAN-15 10.00.00.000000 AM +00:00	+00 07:00:00	AA
3	AA567	LHR	JFK	01-JAN-15 01.00.00.000000 PM +00:00	+00 07:00:00	AA

Statement 54

REM Create an error log table (err$_flights) for the flights table

Create an error log table (err$_flights) for the flights table

Statement 55

begin 
  DBMS_ERRLOG.create_error_log (dml_table_name => 'flights'); 
end;

Statement processed.

Statement 56

REM Update a query with check operation.

Update a query with check operation.

Statement 57

REM This validates that the update will not set values that do not match the where clause

This validates that the update will not set values that do not match the where clause

Statement 58

REM Select all the rows with durations of at least 4 hours, preventing

Select all the rows with durations of at least 4 hours, preventing

Statement 59

REM any rows in the table having durations less than 4 hours after the update

any rows in the table having durations less than 4 hours after the update

Statement 60

REM This time the statement will succeed because invalid rows are placed in the error table

This time the statement will succeed because invalid rows are placed in the error table

Statement 61

REM Only that will have an interval greater than or equal to 4 hours after the update are modified

Only that will have an interval greater than or equal to 4 hours after the update are modified

Statement 62

update (select flight_duration  
        from   flights 
        where  flight_duration >= interval '4' hour 
        with check option) 
set    flight_duration = flight_duration - interval '3' hour 
log errors into err$_flights ('UDPATE') reject limit unlimited

3 row(s) updated.

Statement 63

select * from err$_flights

 
ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
1402 ORA-01402: view WITH CHECK OPTION where-clause violation
AAAaquAAOAAAACVAAA U UDPATE 4 AA5670 JFK LHR 01-JAN-15 11.00.00.000000000 AM +00:00 +000000000 03:00:00.000000000 BA
1402 ORA-01402: view WITH CHECK OPTION where-clause violation
AAAaquAAOAAAACVAAB U UDPATE 5 BA124 JFK LHR 02-JAN-15 10.00.00.000000000 PM -05:00 +000000000 02:30:00.000000000 BA
1402 ORA-01402: view WITH CHECK OPTION where-clause violation
AAAaquAAOAAAACVAAC U UDPATE 6 AA567 JFK LHR 01-JAN-15 08.00.00.000000000 PM -05:00 +000000000 03:00:00.000000000 BA
1402 ORA-01402: view WITH CHECK OPTION where-clause violation
AAAaquAAOAAAACVAAD U UDPATE 7 BA986 JFK SFO 02-JAN-15 12.00.00.000000000 PM -05:00 +000000000 03:00:00.000000000 BA

4 rows selected.

ORA_ERR_NUMBER$	ORA_ERR_MESG$	ORA_ERR_ROWID$	ORA_ERR_OPTYP$	ORA_ERR_TAG$	FLIGHT_ID	FLIGHT_NUMBER	DEPARTURE_AIRPORT_CODE	DESTINATION_AIRPORT_CODE	DEPARTURE_DATETIME	FLIGHT_DURATION	OPERATING_CARRIER_CODE
1402	ORA-01402: view WITH CHECK OPTION where-clause violation	AAAaquAAOAAAACVAAA	U	UDPATE	4	AA5670	JFK	LHR	01-JAN-15 11.00.00.000000000 AM +00:00	+000000000 03:00:00.000000000	BA
1402	ORA-01402: view WITH CHECK OPTION where-clause violation	AAAaquAAOAAAACVAAB	U	UDPATE	5	BA124	JFK	LHR	02-JAN-15 10.00.00.000000000 PM -05:00	+000000000 02:30:00.000000000	BA
1402	ORA-01402: view WITH CHECK OPTION where-clause violation	AAAaquAAOAAAACVAAC	U	UDPATE	6	AA567	JFK	LHR	01-JAN-15 08.00.00.000000000 PM -05:00	+000000000 03:00:00.000000000	BA
1402	ORA-01402: view WITH CHECK OPTION where-clause violation	AAAaquAAOAAAACVAAD	U	UDPATE	7	BA986	JFK	SFO	02-JAN-15 12.00.00.000000000 PM -05:00	+000000000 03:00:00.000000000	BA

Statement 64

select * from flights

 
FLIGHT_ID FLIGHT_NUMBER DEPARTURE_AIRPORT_CODE DESTINATION_AIRPORT_CODE DEPARTURE_DATETIME FLIGHT_DURATION OPERATING_CARRIER_CODE
4 AA5670 JFK LHR 01-JAN-15 11.00.00.000000 AM +00:00 +00 06:00:00 BA
5 BA124 JFK LHR 02-JAN-15 10.00.00.000000 PM -05:00 +00 05:30:00 BA
6 AA567 JFK LHR 01-JAN-15 08.00.00.000000 PM -05:00 +00 06:00:00 BA
7 BA986 JFK SFO 02-JAN-15 12.00.00.000000 PM -05:00 +00 06:00:00 BA
1 AA5670 LHR JFK 01-JAN-15 11.00.00.000000 AM +00:00 +00 04:00:00 AA
2 BA123 LHR JFK 02-JAN-15 10.00.00.000000 AM +00:00 +00 04:00:00 AA
3 AA567 LHR JFK 01-JAN-15 01.00.00.000000 PM +00:00 +00 04:00:00 AA

7 rows selected.

UPDATE statement examples

Additional Information