Command Discussion

• The DELETE FROM clause indicates you want to remove a row from a table. Following this clause, the user specifies the name of the table from which data is to be deleted.

• To find the record or records being deleted, use a search condition similar to that used in the SELECT statement.

• Where INDIRECT text columns are concerned, such rows will be deleted just as any other when DELETE FROM is used. However, the files pointed to by INDIRECT will only be removed where managed by Texis, as defined in the previous section on Texis owned files.

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 record:

117  Peters, Robert      SPI    DHEAD  FULL       34000

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.