Changing Lines 

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

UPDATE target lines.

This statement allows you to change one or more lines in the database table target. As described in the section Inserting Table Lines, the database table target can be specified statically and dynamically.

Changing Lines Column by Column

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

UPDATE target SET set₁ set₂ ... [WHERE cond].

The WHERE clause determines the lines that are to be changed. If you do not specify a WHERE clause, all lines will be changed. The expressions set₁ set₂ … are three different SETstatements that determine the columns (set₁ changes column s₁) to be changed, and how they are to be changed:

·        s₁ = f

The value in column s₁ is set to the value f for all lines selected.

·        s₁ = s₁ + f

The value in column s₁ is increased by the value f for all lines selected.

·        s₁ = s₁ - f

The value in column s₁ is decreased by the value f for all lines selected.

f 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 has been changed, sy-subrc is set to 0 – otherwise to 4. sy-dbcnt is set to the number of changed lines.

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

Overwriting Individual Lines with Work Areas

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

UPDATE target FROM wa.

The contents of the work area waoverwrite the line in the database table dbtab that has the same primary key. The work area wa 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, no line is changed, and sy-subrc is set to 4.

A shortened form of the above statement is:

UPDATE dbtab.

In this case, the contents of the table work area dbtab 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 for lines:

UPDATE target FROM TABLE itab.

The contents of the internal table itaboverwrite the lines in the database table dbtab that have the same primary keys. The same rules apply to the line type of itab as to the work area wa 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 are used, 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 the field values of those lines where the primary keys are AA 0064 or LH 0017 are replaced by the values of the work area wa or the table work area spfli. The variant with a table work area declared using TABLES should no longer be used.

 

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.