Commands and Statements

Command DELETE - SQL Foundation

Deletes one or more records in a table. END

DELETE FROM <cTableName> 
       [WHERE <lExpression>] 
       [VIA (<coSession>)]
<cTableName> specifies the name of the table containing the records to be deleted.
WHERE <lExpression>
Specifies one or more filter conditions the records must meet in order to be deleted.

When working with dbf tables records are not physically deleted by the SQL DELETE command. Instead, a "deleted" mark is put on the record so the record can later be "undeleted" using RECALL command. Records are only physically removed from a dbf table by the commands PACK and ZAP.

VIA (<oSession>)
<oSession> optionally specifies the Session context for the operation. oSession is an object of DacSession() or any derived class and must be specified as an object expression in parentheses. If <oSession> is not specified, the SQL DELETE statement is executed by the local SQL engine. If a VIA clause is specified in an SQL statement on the other hand, the statement executes in the context defined by the session which in most cases involves a remote backend.

Records marked for deletion using SQL DELETE are only physically removed from the table when the PACK command is used. Until that point, records which are marked for deletion can be "undeleted" with the RECALL command.

If SET DELETED is ON, the records marked for deletion are ignored by all database commands in which a scope is specified.

Unlike DELETE, the SQL DELETE command can be used to manipulate multiple records at once. If the database is open in shared mode, each record must be locked and unlocked in turn for this operation, which may introduce a substantial delay. For optimal performance it is therefore recommended to open the table in exclusive mode when manipulating multiple records using SQL DELETE. Alternatively, the FLock() function can be used to lock the table as a whole before performing the delete.


If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.