SQL Delete Top Examples Explained

You can use the DELETE clause to delete a row from the table. Either you can delete a row or all the rows from a table, but you cannot drop columns with Delete.

DELETE SQL Query Example
DELETE SQL Query Example

The result of the DELETE statement is the removal of zero or more rows of a table.

The removal depends on the condition given 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:


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.

Note: The DELETE statement may have deleted more than one row. The number of rows deleted is reflected in SQLERRD(3) of the SQLCA.

This value is also available from the ROW_COUNT diagnostics item in the GET DIAGNOSTICS statement.

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.

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.