To remove rows from a table, use the DELETE statement. When you delete a row, you remove the entire row. The DELETE statement does not remove specific columns from the row.
- The result of the DELETE statement is the removal of zero or more rows of a table, depending on how many rows satisfy the search condition specified in the WHERE clause.
- If you omit the WHERE clause from a DELETE statement, SQL removes all the rows from the table.
The DELETE SQL query looks like this:
DELETE FROM table-name WHERE search-condition ...
For example, suppose that department D11 is moved to another site. You delete each row in the CORPDATA.EMPLOYEE table with a WORKDEPT value of D11 as follows:
DELETE FROM CORPDATA.EMPLOYEE WHERE WORKDEPT = 'D11'
The WHERE clause tells SQL which rows you want to delete from the table. SQL deletes all the rows that satisfy the search condition from the base table. Deleting rows from a view deletes the rows from the base table.
You can omit the WHERE clause, but it is best to include one, because a DELETE statement without a WHERE clause deletes all the rows from the table or view. To delete a table definition as well as the table contents, issue the DROP statement.
If SQL finds an error while running your DELETE statement, it stops deleting data and returns a negative SQLCODE. If you specify COMMIT(*ALL), COMMIT(*CS), COMMIT(*CHG), or COMMIT(*RR), no rows in the table are deleted (rows already deleted by this statement, if any, are restored to their previous values).
If COMMIT(*NONE) is specified, any rows already deleted are not restored to their previous values. If SQL cannot find any rows that satisfy the search condition, an SQLCODE of +100 is returned.
- Removing rows from tables with referential constraints
If a table has a primary key but no dependents, or if a table has only foreign keys but no primary key, the DELETE statement operates the same way as it does for tables without referential constraints. If a table has a primary key and dependent tables, the DELETE statement operates according to the delete rules specified for the table.