Committing Database Changes

Open SQL contains the statements

COMMIT WORK.

and

ROLLBACK WORK.

for confirming or undoing database updates. COMMIT WORK always concludes a database LUW and starts a new one. ROLLBACK WORK always undoes all changes back to the start of the database LUW.

These statements are part of the SAP transaction concept, and should only be used in this context.

As well as the COMMIT WORK and ROLLBACK WORK statements, there are other situations where data is implicitly written to the database or rolled back, and where database LUWs therefore begin and end.

Furthermore, the above statements also control SAP LUWs, which extend over several database LUWs.

The ABAP statements COMMIT WORK and ROLLBACK WORK only work like the corresponding Standard SQL statements if the entire application program is represented by a single implicit database LUW, that is, for example, in a single dialog step.

Inserting or Changing Lines

To insert lines into a database table regardless of whether there is already a line in the table with the same primary key, use the following:

MODIFY .

If the database table contains no line with the same primary key as the line to be inserted, MODIFY works like INSERT, that is, the line is added.

If the database already contains a line with the same primary key as the line to be inserted, MODIFY works like UPDATE, that is, the line is changed.

For performance reasons, you should use MODIFY only if you cannot distinguish between these two options in your ABAP program.

You can add or change one or more lines in a database table . You can only insert or change lines in 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 :

MODIFY [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 :

MODIFY () [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.

Inserting or Changing Single Lines

To insert or change a single line in a database table, use the following:

MODIFY FROM .

The contents of the work area are written to 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 data is placed in the database table according to the line structure of the table, and regardless of the structure 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 does not already contain a line with the same primary key as specified in the work area, a new line is inserted. If the database table does already contain a line with the same primary key as specified in the work area, the existing line is overwritten. SY-SUBRC is always set to 0.

A shortened form of the above statement is:

MODIFY .

In this case, the contents of the table work area are inserted into 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.

Inserting or Changing Several Lines

To insert or change several lines in a database table, use the following:

MODIFY FROM TABLE .

Those lines of the internal table for which there is not already a line in the database table with the same primary key are inserted into the table. Those lines of the internal table for which there is already a line in the database table with the same primary key overwrite the existing line in the database table. The same rules apply to the line type of as to the work area described above.

SY-SUBRC is always set to 0. SY-DBCNT is set to the number of lines in the internal table.

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.

Changing Line

Changing Line

The Open SQL statement for changing data in a database table is:

UPDATE .

It allows you to change one or more lines in the database table . You can only change lines in 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 :

UPDATE [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 :

UPDATE () [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.

Changing Lines Column by Column

To change certain columns in the database table, use the following:

UPDATE SET ₁> 2> ... [WHERE ].

The WHERE clause determines the lines that are changed. If you do not specify a WHERE clause, all lines are changed. The expressions _i > are three different SET statements that determine the columns to be changed, and how they are to be changed:

• _i> =

The value in column _i> is set to the value for all lines selected.

• _i> = i> +

The value in column _i> is increased by the value of for all lines selected.

• _i> = i> -

The value in column _i> is decreased by the value of for all lines selected.

can be a data object or a column of the database table. You address the columns using their direct names.

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

If you use SET statements, you cannot specify the database table dynamically.

Overwriting Individual Lines From a Work Area

To overwrite a single line in a database table with the contents of a work area, use the following:

UPDATE FROM .

The contents of the work area overwrite the line in the database table that has the same primary key. 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 data is placed in the database table according to the line structure of the table, and regardless of the structure 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 inserted, and SY-SUBRC is set to 4.

A shortened form of the above statement is:

UPDATE .

In this case, the contents of the table work area are used to update 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.

Overwriting Several Lines Using an Internal Table

To overwrite several lines in a database table with the contents of an internal table, use the following:

UPDATE FROM TABLE .

The contents of the internal table overwrite the lines in the database table that have the same primary keys. The same rules apply to the line type of as to the work area described above.

If the system cannot change 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 processed 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 overwrite more than one line in a database table, it is more efficient to work with an internal table than to change the lines one by one.

Examples

UPDATE SFLIGHT SET PLANETYPE = 'A310'
PRICE = PRICE - '100.00'
WHERE CARRID = 'LH' AND CONNID = '0402'.

This example overwrites the contents of the PLANETYPE column with A310 and decreases the value of the PRICE column by 100 for each entry in SFLIGHT where CARRID contains ‘LH’ and CONNID contains ‘402’.

TABLES SPFLI.

DATA WA TYPE SPFLI.

MOVE 'AA' TO WA-CARRID.
MOVE '0064' TO WA-CONNID.
MOVE 'WASHINGTON' TO WA-CITYFROM.
...
UPDATE SPFLI FROM WA.

MOVE 'LH' TO SPFLI-CARRID.
MOVE '0017' TO SPFLI-CONNID.
MOVE 'BERLIN' TO SPFLI-CITYFROM.
...
UPDATE SPFLI.

CARRID and CONNID are the primary key fields of table SPFLI. All fields of those lines where the primary key fields are "AA" and "0064", or "LH" and "0017", are replaced by the values in the corresponding fields of the work area WA or the table work area SPFLI.

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

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

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

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

...

UPDATE SPFLI FROM TABLE ITAB.

This example fills a hashed table ITAB and then overwrites the lines in SPFLI that have the same primary key (CARRID and CONNID) as a line in the internal table.

Inserting Lines into Tables

The Open SQL statement for inserting data into a database table is:

INSERT INTO .

It allows you to insert one or more lines into the database table . You can only insert lines into 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 :

INSERT INTO [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 :

INSERT INTO () [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.

Inserting a Single Line

To insert a single line into a database table, use the following:

INSERT INTO VALUES .

The contents of the work area are written to 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 data is placed in the database table according to the line structure of the table, and regardless of the structure 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 does not already contain 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 inserted, and SY-SUBRC is set to 4.

You can also insert single lines using the following shortened form of the INSERT statement:

INSERT FROM >.

Using FROM instead of VALUE allows you to omit the INTO clause. Shorter still is:

INSERT .

In this case, the contents of the table work area are inserted into 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.

Inserting Several Lines

To insert several lines into a database table, use the following:

INSERT FROM TABLE [ACCEPTING DUPLICATE KEYS] .

This writes all lines of the internal table to the database table in one single operation. The same rules apply to the line type of as to the work area described above.

If the system is able to insert all of the lines from the internal table, SY-SUBRC is set to 0. If one or more lines cannot be inserted because the database already contains a line with the same primary key, a runtime error occurs. You can prevent the runtime error occurring by using the addition ACCEPTING DUPLICATE KEYS. In this case, the lines that would otherwise cause runtime errors are discarded, and SY-SUBRC is set to 4.

The system field SY-DBCNT contains the number of lines inserted into the database table, regardless of the value in SY-SUBRC.

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

Examples

Adding single lines

TABLES SPFLI.

DATA WA TYPE SPFLI.

WA-CARRID = 'LH'.
WA-CITYFROM = 'WASHINGTON'.
...
INSERT INTO SPFLI VALUES WA.

WA-CARRID = 'UA'.
WA-CITYFROM = 'LONDON'.
...
INSERT SPFLI FROM WA.

SPFLI-CARRID = 'LH'.
SPFLI-CITYFROM = 'BERLIN'.
...
INSERT SPFLI.

This program inserts a single line into the database table SPFLI using each of the three possible variants of the INSERT statement.

Instead of

INSERT SPFLI

in the last line, you could also use the longer forms

INSERT SPFLI FROM SPFLI

or

INSERT INTO SPFLI VALUES SPFLI

The name SPFLI is therefore not unique.

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

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

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

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

...

INSERT SPFLI FROM TABLE ITAB ACCEPTING DUPLICATE KEYS.

IF SY-SUBRC = 0.
...
ELSEIF SY-SUBRC = 4.
...
ENDIF.

This example fills a hashed table ITAB and inserts its contents into the database table SPFLI. The program examines the contents of SY-SUBRC to see if the operation was successful.