Deleting data from tables
You can delete data from a table by deleting one or more rows from the table, by deleting all rows from the table, or by dropping columns from the table.
To delete one or more rows in a table:
- Use the DELETE statement with a WHERE clause to specify
a search condition.
The DELETE statement removes zero or more rows of a table, depending on how many rows satisfy the search condition that you specify in the WHERE clause.
You can use DELETE with a WHERE clause to remove only selected rows from a declared temporary table, but not from a created temporary table.
The following DELETE statement deletes each row in the YEMP table that has an employee number '000060'.
DELETE FROM YEMP WHERE EMPNO = '000060';
When this statement executes, Db2 deletes any row from the YEMP table that meets the search condition.
If Db2 finds an error while executing your DELETE statement, it stops deleting data and returns error codes in the SQLCODE and SQLSTATE variables or related fields in the SQLCA. The data in the table does not change.
If the DELETE is successful, SQLERRD(3) in the SQLCA contains the number of deleted rows. This number includes only the number of deleted rows in the table that is specified in the DELETE statement. Rows that are deleted (in other tables) according to the CASCADE rule are not included in SQLERRD(3).
To delete every row in a table:
- Use the DELETE statement without specifying a WHERE clause.
With segmented table spaces, deleting all rows of a table is very fast.
The following DELETE statement deletes every row in the YDEPT table:
DELETE FROM YDEPT;
If the statement executes, the table continues to exist (that is, you can insert rows into it), but it is empty. All existing views and authorizations on the table remain intact when using DELETE.
- Use the TRUNCATE statement.
The TRUNCATE statement can provide the following advantages over a DELETE statement:
- The TRUNCATE statement can ignore delete triggers
- The TRUNCATE statement can perform an immediate commit
- The TRUNCATE statement can keep storage allocated for the table
The TRUNCATE statement does not, however, reset the count for an automatically generated value for an identity column on the table. If 14872 was the next identity column value to be generated before a TRUNCATE statement, 14872 would be the next value generated after the TRUNCATE statement.
Suppose that you need to empty the data from an old inventory table, regardless of any existing delete triggers, and you need to make the space that is allocated for the table available for other uses. Use the following TRUNCATE statement.
TRUNCATE INVENTORY_TABLE IGNORE DELETE TRIGGERS DROP STORAGE;Suppose that you need to empty the data from an old inventory table permanently, regardless of any existing delete triggers, and you need to preserve the space that is allocated for the table. You need the emptied data to be completely unavailable, so that a ROLLBACK statement cannot return the data. Use the following TRUNCATE statement.
TRUNCATE INVENTORY_TABLE REUSE STORAGE IGNORE DELETE TRIGGERS IMMEDIATE;
- Use the DROP TABLE statement.
DROP TABLE drops the specified table and all related views and authorizations, which can invalidate plans and packages.
To drop columns from a table:
- Use the ALTER TABLE
statement with the DROP COLUMN clause.
Because dropping a column from a table is a pending change to the definition of the table, the table space is placed in advisory REORG-pending status (AREOR). When the pending change is applied (by running the REORG utility with the SHRLEVEL CHANGE or REFERENCE options), the column is dropped from the table, and any dependent packages and statements in the dynamic statement cache are invalidated.