Deleting Lines

Deleting Lines

The Open SQL statement for deleting lines from a database table is:

DELETE [FROM] .

It allows you to delete one or more lines from the database table . You can only delete lines from an ABAP Dictionary view if it only contains fields from one table, and its maintenance status is defined as Read and change. You may specify the database table either statically or dynamically.

Specifying a Database Table

To specify the database table statically, enter the following for :

DELETE [FROM] [CLIENT SPECIFIED] .

where is the name of a database table defined in the ABAP Dictionary.

To specify the database table dynamically, enter the following for :

DELETE [FROM] () [CLIENT SPECIFIED] .

where the field contains the name of a database table defined in the ABAP Dictionary.

You can use the CLIENT SPECIFIED addition to disable automatic client handling.

Selecting Lines Using Conditions

To select the lines that you want to delete using a condition, use the following:

DELETE FROM WHERE .

All of the lines in the database table that satisfy the conditions in the WHERE clause are deleted. The FROM expression must occur between the keyword and the database table.

You should take particular care when programming the WHERE clause to ensure that you do not delete the wrong lines. For example, if you specify an empty internal table in a dynamic WHERE clause, all of the lines in the table are deleted.

If at least one line is deleted, the system sets SY-SUBRC to 0, otherwise to 4. SY-DBCNT contains the number of lines deleted.

Selecting Single Lines Using Work Areas

Instead of using a WHERE clause, you can select lines for deletion using the contents of a work area. In this case, you would write:

DELETE FROM .

This deletes the line with the same primary key as the work area . The FROM expression must not occur between the keyword and the database table. The work area must be a data object with at least the same length and alignment as the line structure of the database table. The key is read according to the structure of the table line, and not that of the work area. It is a good idea to define the work area with reference to the structure of the database table.

If the database table contains a line with the same primary key as specified in the work area, the operation is completed successfully and SY-SUBRC is set to 0. Otherwise, the line is not deleted, and SY-SUBRC is set to 4.

A shortened form of the above statement is:

DELETE .

In this case, the contents of the table work area are used to delete from the database table with the same name. You must declare this table work area using the TABLES statement. In this case, it is not possible to specify the name of the database table dynamically. Table work areas with the same name as the database table (necessary before Release 4.0) should no longer be used for the sake of clarity.

Selecting Several Lines Using an Internal Table

You can also use an internal table to delete several lines:

DELETE FROM TABLE itab .

This deletes all lines from the database that have the same primary key as a line in the internal table . The same rules apply to the line type of as to the work area described above.

If the system cannot delete a line because no line with the specified key exists, it does not terminate the entire operation, but continues processing the next line of the internal table.

If all lines from the internal table have been processed, SY-SUBRC is set to 0. Otherwise, it is set to 4. If not all lines are used, you can calculate the number of unused lines by subtracting the number of deleted lines in SY-DBCNT from the total number of lines in the internal table. If the internal table is empty, SY-SUBRC and SY-DBCNT are set to 0.

Whenever you want to delete more than one line from a database table, it is more efficient to work with an internal table than to insert the lines one by one.

Examples

DELETE FROM SFLIGHT WHERE PLANETYPE = 'A310' AND
CARRID = 'LH'.

This deletes all lines from SFLIGHT where PLANETYPE has the value A310 and CARRID contains the value LH.

TABLES SPFLI.

DATA: BEGIN OF WA,
CARRID TYPE SPFLI-CARRID,
CONNID TYPE SPFLI-CONNID,
END OF WA.

MOVE 'AA' TO WA-CARRID.
MOVE '0064' TO WA-CONNID.
DELETE SPFLI FROM WA.

MOVE 'LH' TO SPFLI-CARRID.
MOVE '0017' TO SPFLI-CONNID.

DELETE SPFLI.

CARRID and CONNID are the primary key fields of table SPFLI. The lines with the primary keys AA 0064 and LH 0017 are deleted.

DATA: BEGIN OF WA,
CARRID TYPE SPFLI-CARRID,
CONNID TYPE SPFLI-CONNID,
END OF WA,
ITAB LIKE HASHED TABLE OF WA
WITH UNIQUE KEY CARRID CONNID.

WA-CARRID = 'UA'. WA-CONNID = '0011'.
INSERT WA INTO TABLE ITAB.

WA-CARRID = 'LH'. WA-CONNID = '1245'.
INSERT WA INTO TABLE ITAB.

WA-CARRID = 'AA'. WA-CONNID = '4574'.
INSERT WA INTO TABLE ITAB.

...

DELETE SPFLI FROM TABLE ITAB.

This example defines a hashed table ITAB with the structure of the primary key of the database table SPFLI. After ITAB has been filled, those lines of the database table are deleted that have the same contents in the primary key fields (CARRID and CONNID) as a line in the internal table.