Delete rows of a table.


Syntax

<delete statement: searched> ::=

DELETE FROM [ schema-name. ] table-name [ [ AS ] correlation-name ]

[ WHERE <search condition> ]


Usage

The DELETE statement is used to delete a selection of rows from the table identified by table-name.


Notes

The current database is implicit if schema-name is not specified.
The WHERE clause restricts the rows that are deleted to those satisfying the search condition.
If no WHERE clause is specified, then all the rows of the table are deleted.

Tip: A more efficient way of deleting all the rows in a table is to drop the table and then recreate it.


Examples

1) The following example deletes students who are not enrolled in any courses:

       DELETE FROM students

       WHERE studentID IN (

SELECT DISTINCT studentID

FROM students s

LEFT JOIN enrolls e ON e.studentID = s.studentID

WHERE e.studentID IS NULL

       )

2) The following example uses a parameter to identify the row to delete:

       DELETE FROM students

       WHERE studentID = :studentID

3) The following example deletes all rows in the ##students table:

       DELETE FROM ##students


Conformance

SQL:2003 standard

-

Core SQL

Home | Site Contents | Documentation | NexusDB Manual V4 | SQL Reference | SQL Statements | Data Statements