Removing rows from a table using the DELETE statement

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 statement 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.