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

DELETE dbtab - cond

Quick Reference

Syntax

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


Extras:

1. ... WHERE sql_cond

2. ... [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 uses a logical expression sql_cond to specify which rows in the database table or view are deleted. The same applies to the logical expression sql_cond as for the WHERE condition of the statement SELECT, with the exception that no subqueries can be executed on the 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.

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 p_carrid TYPE sflight-carrid.
cl_demo_input=>request( CHANGING field = p_carrid ).

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

Addition 2

... [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 that 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. A host variable must be prefixed by the escape character @.

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 pooled tables/ cluster tables or on projection views.

Notes

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 } | ).
  CALL FUNCTION 'DB_COMMIT'.
ENDWHILE.
cl_demo_output=>display( ).