ABAPDELETE_WHERE - DELETE WHERE

rdisp/max_wprun_time - Maximum work process run time   General Material Data  
This documentation is copyright by SAP AG.

DELETE dbtab, cond

Short Reference

... $[WHERE sql_cond$]
    $[ db_hints$]
    $[ORDER BY ... $[OFFSET o$]$] $[UP TO n ROWS$] ...

Additions

1. ... WHERE sql_cond

2. ... db_hints

3. ... $[ORDER BY ... $[OFFSET o$]$] $[UP TO n ROWS$]

Effect

In the variant DELETE FROM target of the statement DELETE, these additions determine the rows to be deleted.

Addition 1

... WHERE sql_cond

Effect

The addition WHERE determines which rows in a DDIC database table or view are deleted. Those rows are deleted for which the logical expression sql_cond is true. The logical expression sql_cond is subject to the restriction that no subqueries can be evaluated on the DDIC database table to be changed. If there is no database row that satisfies the WHERE condition, no rows are deleted and sy-subrc is set to 4. If no WHERE condition is specified, all rows are deleted or as many rows as specified by the addition UP TO. Implicit client handling applies. In a client-specific target, only rows in the current client are deleted by default. The client column of a client-dependent target cannot be specified in the WHERE condition. The addition USING can be used to switch implicit client handling to different clients.

Notes

• If DDIC database tables or DDIC views are accessed using generic table buffering, the buffered area must be specified completely in the WHERE condition. If not, buffering is bypassed.

• If DDIC database tables or DDIC views are accessed using single record buffering, the equality conditions joined using AND in the WHERE condition must be specified for all key fields of the primary key. If not, buffering is bypassed.

• Instead of USING, the obsolete addition CLIENT SPECIFIED can also be used outside strict mode from Release . It disables implicit client handling and the client column can be used in the WHERE condition as an operand.

Example

In the following example, all today's flights of an airline in which no seats are occupied are deleted from the DDIC database table SFLIGHT. See also the example for dtab-source.

Addition 2

... db_hints

Effect

In the variant DELETE FROM TARGET, db_hints can be used to specify database hints.

Addition 3

... $[ORDER BY ... $[OFFSET o$]$] $[UP TO n ROWS$]

Effect

The addition ORDER BY ... is used to sort the rows defined by the WHERE condition. The addition OFFSET is used to delete only rows from the counter o. The addition UP TO restricts the number of rows to delete to n.

The addition OFFSET can only be specified together with ORDER BY .... The addition ORDER BY ... cannot be specified without OFFSET or UP TO. If the addition UP TO is specified without ORDER BY ..., it is not possible to define which of the possible rows are deleted.

n and o expect host variables, host expressions, or literals with the type i, which can represent all non-negative numbers of the value range of i except its maximum value +2,147,483,647. If 0 is specified for n, this maximum value is used. If 0 is specified for o, the addition OFFSET is ignored. Only the types b, s, i, or int8 can be specified. If n or o is specified as a literal or constant, the value 0 is not allowed.

The addition ORDER BY ... has the same syntax and semantics as in the SELECT statement, with the difference that it is not possible to sort explicitly by the client column in the DELETE statement. The SELECT-specific restrictions, on the other hand, are ignored.

The additions OFFSET and UP TO cannot be used when accessing DDIC projection views.

Notes

• The addition UP TO can be used to prevent errors when deleting tables with a large number of entries. Errors of this type can occur, for example, if the internal transaction manager has run out of memory. Exceptions can be raised on the database here if UP TO is not used and not followed by a database commit. These resource bottlenecks can be prevented by distributing the delete action to multiple DELETE statements and hence deleting the corresponding log entries.

• If the addition ORDER BY ... is specified but not sorted uniquely, it is not possible to define which rows are deleted.

• A DELETE statement with the additions OFFSET and UP TO is executed on the database as a single operation, if possible. In databases not supported by the additions OFFSET and ORDER BY, the statements SELECT and DELETE are separated internally. If database table logging is enabled, these statements are always separated. This is generally slower than executing a single database operation.

• When the additions OFFSET or UP TO are used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.

Example

Deletion of entries from a DDIC database table filled using MODIFY in multiple iterations.

ABAP Short Reference   General Data in Customer Master  
This documentation is copyright by SAP AG.

Length: 8341 Date: 20230327 Time: 221332     sap01-206 ( 148 ms )