Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Write Accesses →  DELETE dbtab 

DELETE dbtab - cond

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... [WHERE sql_cond] 

    [db_hints] 

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

Extras

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 delete the rows in question.

Addition 1

... WHERE sql_cond

Effect

The addition WHERE determines which rows in a 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 modified database table. 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 ABAP SQL client handling applies. In a client-specific target, only rows in the current client are deleted by default. The client column of a client-specific target cannot be specified in the WHERE condition. The addition USING can be used to switch implicit client handling to different clients.


Notes

  • If the data source is accessed using generic table buffering, the buffered area must be specified in full in the WHERE condition. If not, buffering is bypassed.
  • If the data sources are accessed using single record buffering, the 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 7.54. 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 database table SFLIGHT (see also, example for dtab-source).

DATA carrid TYPE sflight-carrid. 
cl_demo_input=>request( CHANGING field = carrid ). 

DELETE FROM sflight 
WHERE carrid = @carrid AND 
       fldate = @sy-datum AND 
       NOT seatsocc > 0.

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 rows in question 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, but 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 projection views.


Notes

  • The addition UP TO can be used to prevent errors when deleting tables with a very 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 log entries in question.
  • 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

Deletes a database table filled using MODIFY in multiple iterations.

TYPES bigtab TYPE TABLE OF demo_big_table WITH EMPTY KEY. 

MODIFY demo_big_table FROM TABLE 
  @( VALUE bigtab( FOR i = 1 UNTIL i > 1111111 
     ( id = i value = |{ i }| ) ) ). 

DATA subrc TYPE sy-subrc. 
DATA dbcnt TYPE sy-dbcnt. 
WHILE subrc = 0. 
  DELETE FROM demo_big_table UP TO 100000 ROWS. 
  subrc = sy-subrc. 
  dbcnt = sy-dbcnt. 
  cl_demo_output=>write( |sy-subrc: { subrc 
                       }, sy-dbcnt: { dbcnt } | ). 
  COMMIT CONNECTION default. 
ENDWHILE. 
cl_demo_output=>display( ).