Deleting from a Databse Table

Variants:

1. DELETE FROM  dbtab. or
   
DELETE FROM (dbtabname).

2. DELETE  dbtab      FROM wa. or
   
DELETE (dbtabname) FROM wa.

3. DELETE  dbtab      FROM TABLE itab. or
   
DELETE (dbtabname) FROM TABLE itab.

4. DELETE  dbtab. or
   
DELETE *dbtab.

5. DELETE  dbtab VERSION vers. or
   
DELETE *dbtab VERSION vers.

Effect

Deletes a set of lines in a database table (see relational database). You can specify the name of the database table either directly in the program in the form DELETE FROM dbtab ... or at runtime as the contents of the variable dbtabname in the form DELETE FROM (dbtabname) .... In both cases, the database table must be known to the ABAP Dictionary. By default, the system deletes only data from the current client. You can only use a view to delete data, if the view refers to a single table and was created in the ABAP Dictionary with the maintenance status "no restriction".

DELETE is part of the OPEN SQL command set.

In some cases, the syntax rules that apply to Unicode programs are different than those for non-Unicode programs.See Open SQL and Unicode.

Notes

1. Authorization checks are not supported by the DELETE statement and must be carried out at program level.
   
   
2. When you delete lines using the DELETE command, the process is only complete after a database commit (see LUW). Before the commit, any database change can be reset using a database rollback (see Programming Transactions).
   
   
3. In dialog systems, synchronizing parallel user access to a single dataset cannot be handled by the locking mechanism of the database system alone. In many cases, you therefore have to use the SAP locking mechanism.
   
   
   

Variant 1

DELETE FROM  dbtab. or
DELETE FROM (dbtabname).

Extras:

1. ... WHERE condition

2. ... CLIENT SPECIFIED

3. ... CONNECTION con

Effect

Deletes lines in a database table. If you do not specify a WHERE clause, the system deletes all lines (in the current client). If you specify a WHERE clause, the system deletes all lines that fulfill the WHERE condition.

After the statement is executed, the system field SY-DBCNT contains the number of lines that were deleted.

The Return Code is set as follows:

SY-SUBRC = 0:

At least one line was deleted.

SY-SUBRC = 4:

No line was deleted since no lines were selected.

Example

Delete all flight bookings (in the current client):

DELETE FROM SBOOK.

Addition 1

... WHERE condition

Effect

The system deletes only those lines that fullfill the
condition specified in the WHERE Clause.

Example

Delete all bookings of Lufthansa flight 0400 on 02/28/2001 (in the current client):

DELETE FROM SBOOK WHERE CARRID = 'LH'       AND
                        CONNID = '0400'     AND
                        FLDATE = '20010228'.

Addition 2

... CLIENT SPECIFIED

Effect

Switches off automatic client handling. This allows you
to delete data across all clients in case of client-specific tables. The client field is then handled like a normal table field for which you can specify appropriate conditions in the WHERE clause.

The addition CLIENT SPECIFIED must be specified directly after the name of the database table.

Addition 3

... CONNECTION con

Effect

The Open SQL command is not executed on the
standard database but on the Secondary Database Connection specified using con. con is the name of the database connection as it was specified in the table DBCON in the column CON_NAME. The database connection con can als be specified dynamically in the form (source_text) - the field source_text contains the name of the database connection and must be type C or STRING.
The CONNECTION con addition must be specified directly after the name of the database table or after the CLIENT SPECIFIED addition.

Variant 2

DELETE  dbtab      FROM wa. or
DELETE (dbtabname) FROM wa.

Extras:

1. ... CLIENT SPECIFIED

2. ... CONNECTION con

Effect

These are SAP-specific short forms to delete a single line from a database table. The primary key for the line to be deleted is taken from the explicitly specified work area wa. The key values are read from left to right according to the structure of the primary key of the database table. The structure of wa is not considered. This is why the work area wa must be at least as wide (DATA) as the primary key of the database table dbtab, and the alignment of the work area wa must correspond to that of the database table. Otherwise, a runtime error occurs.
If the database table dbtab or the work area wa contain strings, wa must be compatible with the line structure of dbtab.

After the statement is executed, the sytem field SY-DBCNT contains the number of lines that were deleted (0 or 1).

The Return Code is set as follows:

SY-SUBRC = 0:

The line was deleted.

SY-SUBRC = 4:

No line could be deleted since there was no line with the primary key specified.

Example

Delete the booking with the booking number 3 of Lufthansa flight 0400 on 02/28/2001 (in the current client):

DATA wa TYPE sbook.
wa-carrid = 'LH'.
wa-connid = '0400'.
wa-fldate = '20010228'.
wa-bookid = '00000003'.

DELETE sbook FROM wa.

Addition 1

... CLIENT SPECIFIED

Effect

As in variant 1.

Addition 2

... CONNECTION con

Effect

As in variant 1.

Variant 3

DELETE  dbtab      FROM TABLE itab.
DELETE (dbtabname) FROM TABLE itab.

Extras:

1. ... CLIENT SPECIFIED

2. ... CONNECTION con

Effect

Deletes sets: All lines of the database table for which the internal table itab contains values for the fields of the primary key are deleted. The lines of the internal table itab must fulfill the same conditions as the work area wa in variant 2.

The system field SY-DBCNT contains the number of lines that were deleted, that is, the number of lines in the internal table itab for whose key values lines existed in the database table dbtab.

The Return Code is set as follows:

SY-SUBRC = 0:

All lines in itab could be used for deleting lines in dbtab.

SY-SUBRC = 4:

For at least one line of the internal table, the database table did not contain a line with the same primary key. All lines found were deleted.

Note

If the internal table itab is empty, SY-SUBRC and SY-DBCNT are set to 0.

Addition 1

... CLIENT SPECIFIED

Effect

As in variant 1.

Addition 2

... CONNECTION con

Effect

As in variant 1.

Variant 4

DELETE  dbtab. or
DELETE *dbtab.

Addition:

... CLIENT SPECIFIED

The syntax check performed in an ABAP Objects context is stricter than in other ABAP areas.See Cannot Use Short Forms and Cannot Use * Work Areas.

Note

This variant is obsolete.

Effect

These are the SAP-specific short forms of variant 2. They work like variant 2 but the work area is not specified explicitly. Instead, the table work area dbtab or *dbtab is implicitly used which is declared with a TABLES statement.

• DELETE  dbtab. or
  
• DELETE *dbtab.
  
  
  

is also equivalent to

• DELETE dbtab FROM  dbtab. or
  
• DELETE dbtab FROM *dbtab.
  
  
  

After the statement is executed, the system field SY-DBCNT contains the number of lines that were deleted (0 or 1).

The Return Code is set as follows:

SY-SUBRC = 0:

The line was deleted.

SY-SUBRC = 4:

No line could be deleted since no line with the primary key specified existed.

Example

Delete the booking with the booking number 3 of Lufthansa flight 0400 on 02/28/2001 (in the current client):

TABLES sbook.
sbook-carrid = 'LH'.
sbook-connid = '0400'.
sbook-fldate = '20010228'.
sbook-bookid = '00000003'.

DELETE sbook.

Note

If you do not explicitly specify a work area, the values for the line to be deleted are taken from the table work area dbtab even if the statement occurs in a subroutine (FORM) or function module (FUNCTION), in which the table work area is obscured by an identically named formal parameter or local variable.

Addition

... CLIENT SPECIFIED

Effect

As in variant 1.

Variant 5

DELETE  dbtab VERSION vers.
DELETE *dbtab VERSION vers.

This variant is not allowed in an ABAP Objects context.See Cannot use the VERSION Addition.

Note

This variant has become obsolete since the variants 1 - 3 allow you to specify the database table name dynamically.

Effect

Deletes a line in a database table whose name is taken from the field vers at runtime. The database table must be known to the ABAP Dictionary, and its name must comply with the following naming convention: The name must begin with a 'T' and may contain at most five characters, including the leading 'T'. The field vers must contain the table name without the leading 'T'. The system deletes only lines in the current client. The line to be deleted is taken from the statically specified table work area dbtab or *dbtab.

The Return Code is set as follows:

SY-SUBRC = 0:

The line was deleted.

SY-SUBRC = 4:

No line could be deleted since no line with the primary key specified existed.

Additional help

Deleting Table Lines