Logo

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)

See Also

Delete, Deleted(), Recall, Sys(3054)