An employee whose ID number is 117 has quit his job. Use this statement to delete his record from the EMPLOYEE table.
DELETE FROM EMPLOYEE
WHERE EID = 117 ;
All records which satisfy the search condition are deleted. In this
case, one record is deleted from the table. Note that the entire
117 Peters, Robert SPI DHEAD FULL 34000
is deleted, not just the column specified in the
When you delete records, aim for consistency. For example, if you intend to delete Peters' record in the EMPLOYEE table, you must also delete the reference to Peters as department head in the DEPARTMENT table and so on. This would involve two separate operations.
Example: Let's say we want to delete all the department heads from the EMP_RAISE table as they are not really part of the analysis. Use this statement:
DELETE FROM EMP_RAISE
WHERE RANK = 'DHEAD' ;
The block of all records of employees who are department heads are removed from the EMP_RAISE table, leaving the table with just these entries:
EID ENAME DEPT RANK BENEFITS SALARY 101 Aster, John A. MKT STAFF FULL 32000 103 Chapman, Margaret LIB STAFF PART 22000 104 Jackson, Herbert RND STAFF FULL 30000 106 Sanchez, Carla MKT STAFF FULL 35000 107 Smith, Roberta RND STAFF PART 25000
If the finance analyst wanted to empty the table of existing entries
and perhaps load in new ones from a different part of the
organization, this could be done with this statement:
DELETE FROM EMP_RAISE ;
All rows of EMP_RAISE would be deleted, leaving an empty table. However, the definition of the table has not been deleted; it still exists even though it has no data values, so rows can be added to the table at any time.
It is important to note the difference between the
DELETE command and
the DROP TABLE command. In the former, you eliminate one or more rows
from the indicated table. However, the structure of the table is
still defined, and rows can be added to the table at any time. In the
case of the DROP TABLE command, the table definition is removed from
the system catalog. You have removed not only access to the data in
the table, but also access to the table itself. Thus, to add data to
a "dropped" table, you must first create the table again.