Share via

DELETE - SQL Command

  • Article

Marks records for deletion.

DELETE FROM [DatabaseName!]TableName 
   [WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]

Parameters

  • FROM [DatabaseName!]TableName
    Specifies the table in which records are marked for deletion.

    DatabaseName! specifies the name of a non-current database containing the table. You must include the name of a database containing the table if the database is not the current database. Include the exclamation point (!) delimiter after the database name and before the table name.

  • WHERE FilterCondition1 [AND | OR FilterCondition2 ...]
    Specifies that Visual FoxPro marks only certain records for deletion.

    FilterCondition specifies the criteria that records must meet to be marked for deletion. You can include as many filter conditions as you like, connecting them with the AND or OR operator. You can also use the NOT operator to reverse the value of a logical expression, or use EMPTY( ) to check for an empty field.

Remarks

Records marked for deletion aren't physically removed from the table until PACK is issued. Records marked for deletion can be recalled (unmarked) with RECALL.

If SET DELETED is set to ON, records marked for deletion are ignored by all commands that include a scope.

You can determine the number of records marked for deletion by checking the value of the _TALLY system variable immediately after the DELETE - SQL command.

Unlike DELETE, DELETE - SQL uses record locking when marking multiple records for deletion in tables opened for shared access. This reduces record contention in multiuser situations, but may reduce performance. For maximum performance, open the table for exclusive use or use FLOCK( ) to lock the table.

Example

The following example opens the customer table in the testdata database. DELETE - SQL is used to mark all records for deletion where the country field contains USA. All the records marked for deletion are displayed. RECALL ALL is used to unmark all the records marked for deletion.

CLOSE DATABASES
CLEAR

OPEN DATABASE HOME(2)+"Data\testdata"
USE customer  && Open Customer table

DELETE FROM customer WHERE country = "USA"   && Mark for deletion

CLEAR
LIST FIELDS company, country FOR DELETED( )  && List marked records
* If the file were packed at this point the records would be deleted
WAIT WINDOW "Records currently marked for deletion"+CHR(13) + ;
   "Press any key to revert..."

* Unmark all records marked for deletion
RECALL ALL
CLEAR
* Verify reverted records
COUNT FOR DELETED( )=.T. TO nDeleted

* Convert nDeleted to a character string and display information
WAIT WINDOW ALLTRIM(STR(nDeleted)) + " records marked for deletion."

See Also

DELETE | DELETED( ) | PACK | RECALL | SET DELETED | _TALLY