DELETE - SQL Command
Marks records for deletion.
Note
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, see PACK Command and RECALL Command.
DELETE [Target] FROM [FORCE] Table_List [[, Table_List ...] | [JOIN [ Table_List]]]
[WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]
Parameters
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!]TableName
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
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
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 SELECT - SQL Command.
FORCE specifies that the tables in Table_List are joined in the order they appear in the FROM clause.
Note
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 SELECT - SQL Command - FROM Clause.
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 EMPTY( ) Function.
Remarks
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 SET DELETED Command.
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 _TALLY System Variable.
Tip
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, see DELETE Command and FLOCK( ) Function.
Example 1
Description
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.
Code
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."
Comments
For more information, see OPEN DATABASE Command, USE Command, LIST Commands, CLEAR Commands, COUNT Command, and WAIT Command.
Example 2
Description
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.
Code
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
Comments
For more information and another example, see SQL Language Improvements.