Release 10.1A: OpenEdge Development:
Progress 4GL Reference

> ## DELETE statement

> Removes a record from a record buffer and from the database.

> #### Data movement

> #### Syntax

>     DELETE record
>       [ VALIDATE ( condition , msg-expression ) ]
>       [ NO-ERROR ]

> ``record``

> The name of a record buffer. You can delete a record only after it has been put into a record buffer by a CREATE, FIND, FOR EACH, or INSERT statement.

> If you define an alternate buffer for a table, you can delete a record from that buffer by using the name of the buffer with the DELETE statement.

> To delete a record in a table defined for multiple databases, you must qualify the record’s table name with the database name. See the Record phrase reference entry for more information.

> `VALIDATE ( `condition`, `msg-expression` )`

> Use the VALIDATE option to specify a logical value that allows the deletion of a record when TRUE, but does not allow the deletion of a record when FALSE.

> The `condition` is a Boolean expression (a constant, field name, variable name, or expression) with a value of TRUE or FALSE.

> The `msg-expression` is the message you want to display if the `condition` is FALSE. You must enclose `msg-expression` in quotation marks ("").

> You can also describe delete validation criteria for a table in the Data Dictionary. To suppress the Data Dictionary delete validation criteria for a table, use this VALIDATE option.

>     VALIDATE(TRUE,"")

> If you use the DELETE statement to delete a record in a work table, Progress disregards any VALIDATE option you use with the DELETE statement.

> `NO-ERROR`

> Specifies that any errors that occur when you try to delete the record are suppressed. After the DELETE statement completes, you can check the ERROR-STATUS system handle for information on any errors that might have occurred.

> Examples

> The `r-delet.p` procedure deletes all the records in the customer table.

> r-delet.p
>     FOR EACH customer:
>       DELETE customer.
>     END.

> The `r-delet2.p` procedure prompts the user for a customer number and then displays the name of that customer. It then prompts the user to press y to confirm the deletion of the customer record. The user’s response is stored in the del variable. If the value of the del variable is y, the procedure deletes the customer record.

> r-delet2.p
>     DEFINE VARIABLE del AS LOGICAL FORMAT "y/n".
>     REPEAT:
>       PROMPT-FOR customer.cust-num.
>       FIND customer USING cust-num.
>       DISPLAY name.
>       del = no.
>       UPDATE del LABEL "Enter ""y"" to confirm delete".
>       IF del THEN DELETE customer.
>     END.

> The `r-delval.p` procedure prompts the user for a customer number. The procedure displays the name of the customer and prompts the user: Do you want to delete this customer? If the user answers no, the procedure prompts the user for another customer number. If the user answers yes, the procedure checks whether the customer has orders, using the VALIDATE option. If they do have orders, the procedure displays this message: This customer has outstanding orders and cannot be deleted. If the customer has no orders, the procedure deletes the customer.

> r-delval.p
>     DEFINE VARIABLE ans AS LOGICAL.

>     REPEAT WITH 1 DOWN:
>       PROMPT-FOR customer.cust-num.
>       FIND customer USING customer.cust-num.
>       DISPLAY name.
>       ans = no.
>       DISPLAY "Do you want to delete this customer ?"
>         WITH FRAME f-query.
>       UPDATE ans WITH FRAME f-query NO-LABELS.
>       IF ans
>       THEN DELETE customer
>         VALIDATE(NOT(CAN-FIND(order OF customer)),
>         "This customer has outstanding orders and cannot be deleted.").
>     END.

> #### Notes

> - When you run procedures that delete large numbers of records (for example, a month-end table purge), the process runs much faster if you use the No Crash Protection (-i) parameter in single-user mode. (You must back up your database before using this option.) See OpenEdge Deployment: Startup Command and Parameter Reference for more information on startup parameters.

> - Deleting records does not change the amount of space the database takes up on the disk. Progress re-uses ROWIDs. It does not delete the ROWID when a record is deleted. To recover disk space, you must dump and reload your database.

> - The DELETE statement causes any related database DELETE triggers to execute. All DELETE triggers execute before Progress actually deletes the record. While a DELETE trigger is executing, all FIND requests for the record (even within the trigger) fail, as if the record were already deleted. If a DELETE trigger fails (or executes a RETURN ERROR statement), the corresponding record is not deleted. See OpenEdge Development: Progress 4GL Handbook for more information on database triggers.

> - If a table has both a DELETE trigger and delete VALIDATION, the DELETE trigger executes before the validation is performed.

> - If you have previously retrieved `record` with a field list, the DELETE statement rereads the complete record before deleting it.

> #### See also

> CREATE statement, FIND statement, FOR statement, INSERT statement

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095