ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Write Accesses
DELETE dbtab
Other versions: 7.31 | 7.40 | 7.54
Syntax
DELETE { FROM target [connection] [
WHERE sql_cond] [db_hints]
[ORDER BY ... [OFFSET o]] [UP TO n ROWS] }
| { target [connection] FROM
source }.
Variants
1. DELETE FROM target ...
2. DELETE target FROM source.
Effect
The ABAP SQL statement
DELETE deletes one or more rows from the database table or classic view specified in target. The rows to delete can be specified as follows:
-
In a variant
DELETE FROM targetusing conditions -
In a variant
DELETE target FROMusing data objects insource
The addition connection can be used to specify a
secondary connection.
System Fields
| sy-subrc | Meaning |
|---|---|
| 0 | In the variant DELETE FROM target, at least one row was deleted if aWHERE condition was specified and all or n rowswere deleted if no condition was specified. In the variant DELETE target FROM,the specified row was deleted if a work area was specified in source andall specified rows were deleted if an internal table was specified in source or the internal table is empty. |
| 4 | In the variant DELETE FROM target, no row was deleted if a WHEREcondition was specified or no row was deleted if no condition was specified, since the database tablewas already empty. In the variant DELETE target FROM, no row was deletedif a work area was specified in source or not all specified rows were deleted if an internal table was specified in source. |
The statement DELETE sets sy-dbcnt to the number
of deleted rows. If an overflow occurs because the number or rows is greater than 2,147,483,647, sy-dbcnt is set to -1.
Notes
- The rows are deleted permanently from the database table in the next database commit. Until that point, they can still be undone using a database rollback The current isolation level defines whether the deleted data can be read into other database LUWs before or only after the database commit.
-
The statement
DELETEsets a database lock as an exclusive lock until the next database commit or rollback. If used incorrectly, this can produce a deadlock. - The number of rows that can be deleted from the tables of a database within a database LUW is limited, since a database system can only manage a limited amount of locks and data in the rollback area.
-
The statement
DELETE FROM dbtabhas the statementDELETE FROM itabwith identical syntax. If an internal table has the same name as a database table, a statement like this accesses the internal table.
Variant 1
DELETE FROM target ...
Effect
In the variant DELETE FROM target, either all rows are deleted or the rows
in question are restricted using a WHERE
condition or additions ORDER BY,
OFFSET, and UP TO.
Example
Deletes a row in a database table. The row is specified using a WHERE condition.
DELETE FROM demo_update WHERE id = 'X'.
Variant 2
DELETE target FROM source.
Effect
In the variant DELETE target FROM, either a row specified by a
work area is deleted or multiple rows specified by an internal table are deleted.
Example
Deletes a row in a database table. The row is specified using a work area.
DELETE demo_update FROM @( VALUE #( id = 'X' ) ).