Page 137
INSTRUCTOR_ID TELEPHONE FAX -------------------- ---------- ---------- A612 B331 B391 D201 8055559444 8055550131 D944 E301 E405 8055554455 E491 G331 J505 L391 M101 8055550321 8055550123 R983 S131 T149 W490 Y561 8055550123 17 rows selected.
So far, this chapter has presented the use of conditions in the WHERE clause to restrict the rows that are affected by an UPDATE statement. Now look at how a subquery can be used with an UPDATE statement.
For example, suppose you want to reduce the additional fees for a course by $5 if the additional fees exceed the average additional fees. This can be done with a single UPDATE statement as shown in Listing 6.16. Notice that the subquery must be enclosed in parentheses.
Listing 6.16. Using a subquery with an UPDATE.
SQL> select Department_ID, Course_ID, Additional_Fees 2 from Course 3 order by Department_ID, Course_ID; DEPART COURS ADDITIONAL_FEES ------ ----- --------------- ANTHRO 101 0 ANTHRO 174 55 ANTHRO 189 7.5 BIO 101 55 BIO 177 65 BIO 178 70 ECON 101 25 ECON 189 750
continues
Page 138
Listing 6.16. continued
ECON 199 0
ENG 101 75
ENG 102 45
ENG 103 35
ENG 199 45
ENGL 101 0
ENGL 189 0
ENGL 192 0
ENGL 193 0
HIST 115 0
HIST 184 0
HIST 199 0
MATH 101 0
MATH 189 0
MATH 50 0
MATH 51 10
PHILO 101 0
PHILO 174 0
PHILO 198 0
PSYCH 101 25
PSYCH 181 75
PSYCH 183 45
PSYCH 185 20
PSYCH 501 75
32 rows selected.
SQL> update Course
2 set Additional_Fees = Additional_Fees - 10
3 where
4 Additional_Fees >
5 (select avg(Additional_Fees) from Course);
8 rows updated.
SQL> select Department_ID, Course_ID, Additional_Fees
2 from Course
3 order by Department_ID, Course_ID;
DEPART COURS ADDITIONAL_FEES
------ ----- ---------------
ANTHRO 101 0
ANTHRO 174 45
ANTHRO 189 7.5
BIO 101 45
BIO 177 55
BIO 178 60
ECON 101 25
ECON 189 740
ECON 199 0
ENG 101 65
ENG 102 45
ENG 103 35
ENG 199 45
Page 139
ENGL 101 0 ENGL 189 0 ENGL 192 0 ENGL 193 0 HIST 115 0 HIST 184 0 HIST 199 0 MATH 101 0 MATH 189 0 MATH 50 0 MATH 51 10 PHILO 101 0 PHILO 174 0 PHILO 198 0 PSYCH 101 25 PSYCH 181 65 PSYCH 183 45 PSYCH 185 20 PSYCH 501 65 32 rows selected.
The DELETE statement removes rows from a table. You don't need to know the physical ordering of the rows in a table to perform a DELETE. Oracle uses the criteria in the WHERE clause to determine which rows to delete; the Oracle database engine determines the internal location of the rows.
The DELETE statement has the simplest syntax of the four DML statements:
DELETE FROM table_name [WHERE condition]
The variables are defined as follows:
table_name is the table to be updated.condition is a valid SQL condition.
If you think that the SQL syntax is inconsistent, you're correct. For example, the syntax
for the UPDATE statement (UPDATE table_name) differs from the syntax of the
DELETE statement (DELETE FROM table_name). SQL has many other idiosyncrasies, and they aren't going to
go away soon. If you want to take advantage of the power in SQL, concentrate on learning
the syntax and working through a lot of examples. Listing 6.17 displays an example of a
simple DELETE statement.
Page 140
Listing 6.17. Example of a DELETE statement.
SQL> delete from Class 2 where 3 Department_ID = `BIO'; 1 row deleted.
| NOTE |
A DELETE statement will execute successfully, even if no rows are deleted from the table.SQL> delete from Class 2 where 3 Department_ID = `ANTHRO'; 0 rows deleted. |
In designing an application, you may need to delete all the rows in a table. If the table has many rows, using a DELETE to accomplish this task can be quite inefficient. As an alternative, you should consider using the TRUNCATE TABLE statement. The TRUNCATE TABLE statement deletes rows much faster than the DELETE statement does.
The TRUNCATE TABLE statement is typically used in the following way:
TRUNCATE TABLE table_name
The variable is defined as follows:
table_name is the table to be truncated.
One caveat: the TRUNCATE TABLE statement is not a DML statement. Therefore, if you issue a TRUNCATE TABLE statement, you cannot change your mind and perform a rollback to recover the lost rows; the TRUNCATE TABLE statement is a one way trip.
An ideal information system provides concurrent access to multiple users. Of course, you
also want to be sure that one user cannot step on another user's changes. For example, if
one administrator in the anthropology department changes the description of a course,
another administrator in the same department should not be able to change any information
about the same course until the first administrator has committed her changes.
Page 141
By the same token, the fact that an administrator in one department is changing information about a course offered by the department should not prevent anyone else from viewing the current information about that course or changing information about a different course.
In a multiuser Oracle environment, Oracle provides what is termed "read consistency" at the SQL statement level. Read consistency means that a single SQL statement cannot return results that are contradictory or inconsistent. Listing 6.18 presents an example of this concept.
Suppose the chairperson of the biology department wants to know the number of units for each of the courses offered by the department.
Listing 6.18. Example of read consistency.
SQL> select Course_ID, Title, Units
2 from Course
3 where
4 Department_ID = `BIO'
5 order by Course_ID;
COURSE_ID TITLE UNITS
-------------------- ------------------------------ ---------
101 INTRO TO BIOLOGY 3
177 INVERTEBRATE ANATOMY 3
178 MAMMALIAN ANATOMY 3
Just after the department head's query was submitted to the database, the college registrar updated all three-unit courses to four units. Statement-level consistency means that the query cannot return a row in which the number of units has been updated to four. Depending on when the college registrar commits her changes, the biology chairperson either sees the change or doesn't, but she will never see a partial change manifested in a single SQL statement.
However, even though Oracle provides consistency within a single SQL statement, its default behavior doesn't guarantee read consistency during more than one statement. If you query a table twice, you may obtain different results the second time if another Oracle user changes the table between your first and second queries.
You may encounter a situation in which you need more than single-statement read consistency. In fact, you may need to have read consistency across a particular transaction. For this purpose, you need to issue the following statement:
set transaction read only;