MODIFY GENERAL

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)

View Updates

Copyright © 2002 by Tamar E. Granor, Ted Roche, Doug Hennig, and Della Martin. All Rights Reserved.