We are hiring and constantly growing! Have a look through our vacancies to find the right role for you!
... $[WHERE sql_cond$]
$[ORDER BY ... $[OFFSET o$]$] $[UP TO n ROWS$] ...
1. ... WHERE sql_cond
2. ... db_hints
3. ... $[ORDER BY ... $[OFFSET o$]$] $[UP TO n ROWS$]
In the variant DELETE FROM target of the statement DELETE, these additions determine the rows to be deleted.
... WHERE sql_cond
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.
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.
In the variant DELETE FROM TARGET, db_hints can be used to specify
... $[ORDER BY ... $[OFFSET o$]$] $[UP TO n ROWS$]
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.
Deletion of entries from a DDIC database table filled using MODIFY in multiple iterations.