Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.
Delete-SQL
This is the “other DELETE.” Like the Xbase DELETE, it lets you mark one or more records in a table for deletion. The records aren’t physically deleted until you PACK the table.
Usage
DELETE FROM [ Database! ] Table
[ WHERE lCondition ]
|
Parameter |
Value |
Meaning |
|
Database |
Name |
Name of database containing the table from which records are to be deleted. |
|
Omitted |
Delete records from a free table or the named table in the current database. |
|
|
Table |
Name |
Table from which to delete records. |
|
lCondition |
Logical |
Determines which records are deleted. |
|
Omitted |
All records in cTable are deleted. |
Be careful with this command. Unlike the Xbase DELETE, the default for DELETE-SQL is to delete all records in the table.
Because the two-step delete is an Xbase concept, there’s no SQL equivalent to RECALL. You have to use the Xbase RECALL command.
Despite its SQL antecedents, DELETE-SQL only lets you delete records in a single table. Because you can list only one table in the FROM clause, there’s no way to create a join condition with another table. However, you can use a sub-query in the WHERE clause to base the deletion decision on information from other tables. Functionally, DELETE-SQL doesn’t provide anything that Xbase DELETE doesn’t already have. The only slight advantage it offers is that the table doesn’t need to be open initially, but it leaves it open afterward anyway.
You might think it would be better to use DELETE-SQL when dealing with remote data. But the truth is that you’ll always be working on a view of that data, and Xbase DELETE works just as well. We’re willing to have our opinion changed, but right now we can’t see a whole lot of reasons to use DELETE-SQL.
Example
* Delete all orders for a specified customer.
DELETE FROM TasTrade!Orders WHERE customer_id="WOLZA"
* Delete all orders for the current customer record.
* Assumes Customer is open
DELETE FROM TasTrade!Orders ;
WHERE Customer_id=Customer.Customer_id
* Get rid of customers who've never bothered to place an order.
DELETE FROM TasTrade!Customer ;
WHERE Customer_id NOT IN ;
(SELECT Customer_ID FROM Orders)