- 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
- 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
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.
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
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
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.
Copyright © Thunderstone Software Last updated: Sun Mar 17 21:14:49 EDT 2013