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 definition in 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 the VALIDATE option as follows:

VALIDATE(TRUE,"")

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

NO-ERROR

The NO-ERROR option is used to prevent the statement from raising ERROR and displaying error messages.

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 NO-UNDO FORMAT "y/n".

REPEAT:
  PROMPT-FOR Customer.CustNum.
  FIND Customer USING Customer.CustNum.
  DISPLAY Customer.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 NO-UNDO.

REPEAT WITH 1 DOWN:
  PROMPT-FOR Customer.CustNum.
  FIND Customer USING Customer.CustNum.
  DISPLAY Customer.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. The AVM 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 the AVM 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.
• 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, NO-ERROR option