Deleting rows

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 DELETE, the search condition can be as complicated as necessary. 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