Previous | Table of Contents | Next

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.SQL—Using 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.SQL—Using 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

Deleting Records from Your Table

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.SQL—Deleting 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.SQL—Deleting 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.

Previous | Table of Contents | Next