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.