User's Guide

Part II. Tutorials

Chapter 10. Updating the Database

Sometimes you will want to remove rows from a table. Suppose Rodrigo Guevara (employee ID 249) leaves the company. The following statement deletes Rodrigo Guevara from the employee table.

     DELETE
     FROM employee
     WHERE emp_id = 249

Example: using the DELETE command

You can delete more than one row with one command. For example, the following statement would delete all employees who had a termination date that is not NULL from the employee table.

     DELETE
     FROM employee
     WHERE termination_date IS NOT NULL

This example would not remove any employees from the database as the termination_date column is NULL for all employees.

With UPDATE and DELETE, the search condition can be as complicated as you need. For example, if the employee table is being reorganized, the following statement removes from the employee table all employees in the 617 area code with employee ID 902 as manager. This WHERE clause is a compound search condition including a function (LEFT).

     DELETE
     FROM employee
     WHERE LEFT( phone, 3 ) = '617'
     AND manager_id = 902

Since you have made changes to the database that you do not want to keep, you should undo the changes as follows:

     ROLLBACK