Open SQL Delete

DELETE statement used to delete rows from the table.
DELETE statement allows to delete one or more rows from the database table db-table.

We can only delete rows from an ABAP Dictionary view if it is created one table, and its maintenance status is defined as Read and change.
We may specify the database table db-table either statically or dynamically. Deleting data can be done in two ways and those are –

• Delete rows using WHERE condition
• Delete single row using table work area
• Delete multiple lines using internal table

Delete rows using WHERE condition –

To select the rows using a condition for delete, use the following syntax –

DELETE FROM db-table WHERE condition.

All rows in the database table that satisfy the conditions in the WHERE clause are deleted. The FROM keyword should code between the keyword and the database table.

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.

Delete single record using table work area –

We can select row for deletion using the work area contents, use following syntax –

DELETE db-table FROM work-area.

db-table is the name of a ABAP Dictionary database table and work-area is the table work area.

The above DELETE statment deletes the row with the same key that is specified in the work area work-area. The FROM keyword should code between the keyword and the database table. The work area work-area should be of the same type, length and alignment as the database table structure. It is a good idea to define the work area with reference to the database table structure.

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

Deleting multiple lines using internal table –

We can also use an internal table to delete several lines, use following syntax –

DELETE db-table FROM TABLE internal-table 

db-table is the name of a target database table and internal-table is the name of the source internal table.

The above statement deletes all row from the database that have the same key as a line in the internal table internal-table.

If the system cannot delete a row because no row with the specified key exists, it does not terminate the entire operation, however 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. SY-DBCNT contains the number of rows deleted from database table. Otherwise, it is set to 4. If not all lines are used for deletion, we 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.

If we want to delete more than one line from a database table, it is more efficient way to delete the rows one by one.

*&---------------------------------------------------------------------*
*& Report  Z_OPENSQL
*&---------------------------------------------------------------------*
*& Written by TutorialsCampus
*&---------------------------------------------------------------------*

REPORT  Z_OPENSQL.

TABLES ZTC_TPRODUCT.
DATA: WA LIKE ZTC_TPRODUCT.

*Modifying table with Work area.
DELETE FROM ZTC_TPRODUCT WHERE PRODUCTID = 'IFB4'.

*Error handling
IF SY-SUBRC  0.
  WRITE: 'Data got Deleted, Return code is: ', SY-SUBRC.
ELSE.
  WRITE 'Data deleted successfully..'.
ENDIF.