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