Page 155
Theodore Roosevelt Eleanor Roosevelt Franklin Roosevelt
You can also use select statements in the update statement. Listing 7.5 contains a statement that changes the first_name and last_name columns to upper case if the employee has not purchased glasses. This example also demonstrates that you can modify several columns with the same update statement. When updating multiple columns, you must place a comma after each assignment expression. If the new value is a string, you must enclose it in single quotation marks.
Listing 7.5L_07_05.SQLUsing a Select Statement in the Where Clause of an Update Statement to Identify Records to Update
SQL> update employee 2 set first_name = upper(first_name), 3 last_name = upper(last_name) 4 where payroll_number not in ( 5 select fk_payroll_number from glasses); 4 rows updated. SQL> select first_name, last_name from employee; FIRST_NAME LAST_NAME --------------- --------------- WILLIAM Taft Theodore Roosevelt SUSAN Anthony Eleanor Roosevelt CALVIN Coolidge LYNDON Johnson RONALD Reagan GEORGE BUSH ANDREW Johnson WILLIAM CLINTON JIMMY Carter GERALD Ford RICHARD Nixon JOHN KENNEDY DWIGHT Eisenhower HAROLD Truman Franklin Roosevelt HERBERT HOOVER WOODROW Wilson 19 rows selected.
Page 156
The values clause can also contain a select statement because the result of a select statement is a value. The statement must only return one record. SQL*PLUS does not enable you to update one column with multiple values. Listing 7.6 contains an example of an update statement that uses a select statement to populate the multiple columns listed in the values clause. The statement selects the values for the street column for Franklin Roosevelt and updates the same column in Eleanor Roosevelt's record.
Listing 7.6L_07_06.SQLUsing a Select Statement as a Value in an Update Statement
SQL> select last_name, first_name, street from employee 2 where last_name = `Roosevelt'; LAST_NAME FIRST_NAME STREET --------------- --------------- -------------------- Roosevelt Theodore 12 BROADWAY Roosevelt Eleanor 123 W 57 TH Roosevelt Franklin 12 CHERRY LANE SQL> update employee 2 set street = (select street from employee 3 where last_name = `Roosevelt' 4 and first_name = `Franklin') 5 where last_name = `Roosevelt' 6 and first_name = `Eleanor'; 1 row updated. SQL> select last_name, first_name, street from employee 2 where last_name = `Roosevelt'; LAST_NAME FIRST_NAME STREET --------------- --------------- -------------------- Roosevelt Theodore 12 BROADWAY Roosevelt Eleanor 12 CHERRY LANE Roosevelt Franklin 12 CHERRY LANE
To delete records from tables, use the delete command. The statement contains two parts: the name of the table, and the optional where clause that limits or identifies the records to be deleted. When the where clause is not contained in the delete statement, all the records in the table will be deleted. Listing 7.7 illustrates a simple command that deletes all the records from the glasses table. Notice, SQL*PLUS displays the number of records that were deleted following execution of the command. If the number of records does not match what you expect, you
Page 157
can restore them by using the rollback procedure. The rollback command that restores the deleted records. You can see that the first select in the listing produces no records. It is performed immediately after the delete command was performed. After the rollback command is isssued, the select statement produces this original set of records. The reason the records were not permanently removed will be discussed in the next section.
Listing 7.7L_07_07.SQLDeleting All the Records from the Employee, and Restoring Them with the Rollback Command
SQL> delete from employee; 19 rows deleted. SQL> select last_name from employee; no rows selected SQL> rollback; Rollback complete. SQL> select last_name from employee; LAST_NAME --------------- TAFT ROOSEVELT ANTHONY ROOSEVELT COOLIDGE JOHNSON REAGAN BUSH JOHNSON CLINTON CARTER FORD NIXON KENNEDY EISENHOWER TRUMAN ROOSEVELT HOOVER WILSON 19 rows selected.
Listing 7.8 illustrates a delete command containing a where clause.
Page 158
Listing 7.8L_07_08.SQLDeleting Specific Records from the Employee Table by Using the Where Clause
SQL> delete from glasses where 2 fk_payroll_number > 21; 14 rows deleted. SQL> rollback; Rollback complete.
When records are deleted, the database manager places a copy of each record into the database redo file. This is done because you have the option of having Oracle restore the deleted records if you made a mistake. This is a good feature except for two problems: When a large amount of records is being deleted, it takes a long time to write these records into another table on the disk; in addition, these records may fill up the temporary storage and cause the command to abort.
NOTE |
The only time I use the delete command in SQL*PLUS is when I am converting data from an old system to a new one. The reason is most users and developers do not have the privileges to use the delete command against production data. Having this privilege would allow users to accidently delete the records in a database. It is much safer to manage records through the applications. |
When I am converting data, I spend a lot of time running my load programs and testing the results. When I find an error, I fix the problem and rerun it. I generally make a lot of errors before the programs run properly. This means that I may spend a lot of time waiting for Oracle to write the records to the archive file. I use the delete command when there are a few records in the table or if I want to delete a subset of the table's records. Deleting the records in a large table is a very time consuming practice. Rather than watch the cursor blink while records are placed in the redo file, I often use the truncate command.n
The command removes all the rows from a table. It is much faster than the delete command because it does not write the records to the redo file. Truncate is a very useful command to use when developing conversion programs (see Figure 7.2). The one drawback is that you cannot undo this command. Listing 7.9 illustrates this command. The drop option releases the space from the deleted rows. If you specify the reuse option, the space remains allocated for new rows in the table.