DELETE - SQL Command
Marks records for deletion.
Records marked for deletion are not physically removed from the table until the PACK command is issued. You can recall (unmark) records for deletion by using the RECALL command. For more information, seeand .
DELETE [Target] FROM [FORCE] Table_List [[, Table_List ...] | [JOIN [ Table_List]]] [WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]
- DELETE [Target]
Specifies a target table, cursor, table or cursor alias, or file for the delete operation. If the FROM specifies more than one table, you must include the Target parameter. Target can have the following syntaxes:
DatabaseName! specifies the name of a database containing the table if the table is in a noncurrent database. If the table is in a noncurrent database, you must include the name of the database. Use an exclamation point (!) as a delimiter immediately following the database name and preceding the table name.
TableName specifies the name of a table for the delete operation.
Alias specifies an alias that matches a table in the FROM clause or a cursor in the current data session for the delete operation.
FileName specifies the name of a file for the delete operation.
- FROM [FORCE] Table_List[[, Table_List...] | [JOIN [Table_List]]]
Specifies one or more tables containing the data for the delete operation.
The FROM clause has the same syntax as in the SQL SELECT command except for the following restrictions:
The target table or cursor cannot be included in an OUTER join as a secondary table or cursor.
It should be possible to evaluate all other JOIN operations before performing a JOIN operation on the target table.
The target cursor cannot be the result from a subquery.
For more information, see.
FORCE specifies that the tables in Table_List are joined in the order they appear in the FROM clause.
If FORCE is omitted, Visual FoxPro attempts to optimize the delete operation. However, the delete operation might be executed faster by including the FORCE keyword to disable Visual FoxPro delete optimization.
Table_List can have the following syntaxes:
[DatabaseName!]Table [[AS] Local_Alias]
DatabaseName! specifies the name of a database containing the table if the table is in a noncurrent database. If the table is in a noncurrent database, you must include the name of database. Use an exclamation point (!) as a delimiter immediately following the database name and preceding the table name.
Table specifies the name of the table or cursor you want to retrieve data from. If no table is open, Visual FoxPro displays the Open dialog box so you can specify the file location. After the table opens, it remains open when the query is complete.
Local_Alias specifies a temporary name for the table specified in Table. If you specify a local alias, you must use the local alias instead of the table name throughout the DELETE statement. The alias can represent a table or a cursor.
JOIN provides the capability for specifying one or more secondary tables. There is no hard-coded limit on the number of tables, aliases, or JOIN clauses per DELETE statement.
(Subquery) AS Subquery_Alias
Subquery specifies a SELECT statement within another SELECT statement. For more information about subqueries in SELECT statements, see .
- WHERE FilterCondition1 [AND | OR FilterCondition2...]
Specifies one or more filter conditions that records must meet to be deleted. There is no limit to the number of filter conditions in the WHERE clause.
To reverse the value of a logical expression, use the NOT operator. To check for an empty field, use the EMPTY( ) function. For more information, see.
If the SET DELETED command is set to ON, records marked for deletion are ignored by all commands that include a scope. For more information, see.
To determine the number of records updated, check the value of the _TALLY system variable immediately after executing the SQL DELETE command. For more information, see.
When marking multiple records for deletion in a table opened for shared access, SQL DELETE uses record locking, unlike the DELETE command. This reduces record contention in multiuser situations but might reduce performance. For maximum performance, open the table for exclusive use or use the FLOCK( ) function to lock the table. For more information, seeand .
The following example uses the OPEN DATABASE command to open the Visual FoxPro sample database, TestData.dbc. The USE command opens the Customer table in TestData.dbc. The SQL DELETE statement marks those records for deletion where the Country field contains "USA". The CLEAR command erases the main Visual FoxPro window. The LIST command lists all records marked for deletion. Following the LIST command, if the PACK command was called, the marked records would be deleted.
The RECALL ALL command unmarks all the records marked for deletion. The COUNT command verifies the number of records marked for deletion by counting them. WAIT WINDOW displays that number of records that were marked for deletion.
CLOSE DATABASES CLEAR OPEN DATABASE HOME(2)+"Data\TestData" USE Customer DELETE FROM Customer WHERE country = "USA" CLEAR LIST FIELDS company, country FOR DELETED( ) WAIT WINDOW "Records currently marked for deletion"+CHR(13) + ; "Press any key to revert..." RECALL ALL CLEAR COUNT FOR DELETED( )=.T. TO nDeleted WAIT WINDOW ALLTRIM(STR(nDeleted)) + " records marked for deletion."
For more information, see, , , , , and .
The following code shows a correlated delete in which records in a MyProducts cursor are marked for deletion based on the value of the Discontinued Field in the MSRPList cursor. The WHERE clause specifies the criteria for matching records and the condition evaluated for marking the records.
CLOSE DATABASES ALL CREATE CURSOR MyProducts (ProdID I , OurPrice Y NULL) INSERT INTO MyProducts VALUES (1,1.10) && matches but is not marked for deletion INSERT INTO MyProducts VALUES (2,2.20) && matches and is marked INSERT INTO MyProducts VALUES (3,3.30) && matches and is marked CREATE CURSOR MSRPList (ProdID I , ProdCategory I Null, MSRP Y, Discontinued L) INSERT INTO MSRPList VALUES (1, 9, 1.00, .f.) INSERT INTO MSRPList VALUES (2, 8, 2.00, .t.) INSERT INTO MSRPList VALUES (3, 7, 3.00, .t.) DELETE MyProducts FROM MSRPList ; WHERE MSRPList.ProdID = MyProducts.ProdID; AND MSRPList.discontinued = .t. SELECT MyProducts BROWSE
For more information and another example, see.