Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Writes →  UPDATE dbtab 

UPDATE dbtab - source

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... { {SET set_expression1,
set_expression2, ... [WHERE sql_cond]} 

    | {FROM wa|{TABLE itab}} }.

Alternatives

1. ... SET set_expression1, set_expression2, ... [WHERE sql_cond]

2. ... FROM wa
3. ... FROM TABLE itab

Effect

The data in source defines which rows and columns are changed. Either individual columns are changed using the addition SET, or entire rows are overwritten using the addition FROM.

A wa data object that is not table-like or an itab internal table can be specified after FROM. The content of these objects determines which rows are changed and which values are used to overwrite the row(s). The escape character @ should precede the work area name or the internal table name (as should be the case with every host variable).


Note

Host variables without the escape character @ are obsolete. The escape character @ must be specified in the strict modes of the syntax check from Release 7.40, SP05.

Alternative 1

... SET set_expression1, set_expression2, ... [WHERE sql_cond]

Effect

After the addition SET, the changes are specified in a list of change expressions: set_expression1, set_expression2, ....

The addition WHERE uses a logical expression sql_cond to define in which rows of the database table the changes are executed. For the logical expression sql_cond, the same applies as to the WHERE condition of the statement SELECT, with the exception that no subqueries are to be evaluated in the database table to be changed. If no WHERE condition is specified, all the rows in the database table are changed.

The content of primary key fields can only be changed if the respective database table is not linked with a search help and if pool and cluster tables are not accessed. If these changes would create a row which would cause double entries in the primary key or a unique secondary index of the database table, then no rows are changed and sy-subrc is set to 4.

If the value of a column with type LRAW or LCHR is modified, the associated INT2 or INT4 field must also be given a value.


Notes

  • When a comma-separated list is used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.
  • Instead of using commas, spaces can be used to separate change expressions in an obsolete form. Commas must be specified, however, in the strict modes of the syntax check from Release 7.40, SP05.


Example

UPDATE, Use of SET.

Alternative 2

... FROM wa

Effect

If a non-table-like work area wa is specified, the system searches for a row in the database table which has the same content in its primary key as the corresponding beginning part of the work area. The work area must fulfill the prerequisites for use in Open SQL statements.

  • If a work area which does not contain any reference variables for LOB handles is specified, the content of the work area is interpreted without being converted and follows the structure of the database table or the view. The content of the work area is assigned to the rows found. The assignment takes place without conversion, from left to right following the structure of the database table or the view.
  • When a LOB handle structure is specified, it must be constructed (in accordance with the prerequisites) exactly like the structure of the database table. Work area components that are not LOB handle components are assigned directly to the corresponding column of the row found. In the case of a LOB handle component of a reader stream type, this type is created. In this case of a type for a locator: this must exist and is used as a source. For details, see LOB handles.

If there is no row with the same content for the primary key in the database or if the change would lead to a double entry in a unique secondary index, the row is not changed and sy-subrc is set to 4.

By default, an automatic client handling is performed, which means that a client identifier specified in wa is not considered, but the current client is used instead. The content of wa is not affected. Automatic client handling can be switched off using the addition CLIENT SPECIFIED.


Notes

  • The work area wa should always be declared in relation to the database table or the view in ABAP Dictionary. For the derivation of LOB handle structures, there are special additions of the statements TYPES and [CLASS-]DATA.
  • If the database table or view is specified statically, a short form can be specified outside of classes. This means that the work area specified using FROM wa can be omitted. The prerequisite is that a table work area dbtab for the respective database table or the view is declared using the statement TABLES. The system expands the UPDATE statement implicitly to include the addition FROM dbtab.


Example

Change the discount rate of customer with customer number '00017777' (in the current client) to 3 per cent.

DATA wa TYPE scustom. 

SELECT SINGLE * 
       FROM scustom 
       WHERE id = '00017777' 
       INTO @wa. 

wa-discount = '003'. 
UPDATE scustom FROM @wa.

Alternative 3

... FROM TABLE itab

Effect

If an internal table itab is specified, the system processes all the rows of the internal table in accordance with the rules for the work area wa, with the exception that when specifying an internal table, locators are used as a source but reader streams cannot be created.

The row type of the internal table must fulfill the prerequisites for use in Open SQL statements.

If there is no row with the same content of the primary key in the database (for a row of the internal table) or if the change would lead to a double entry in a unique secondary index, the row is not changed and sy-subrc is set to 4. If the internal table is empty, no rows are changed. However sy-subrc is still set to 0. The system field sy-dbcnt is set to the number of rows that are inserted.


Note

When an internal table is used, package by package processing leads to a read access (running in parallel to an UPDATE on the same rows) that partially shows the new status and partially shows the old status.


Example

Reduction of the flight cost for all of today's flights of an airline carrier in the database table SFLIGHT by the percentage percent. The calculation of the new price is always carried out in an internal table sflight_tab and the database table is changed accordingly.

PARAMETERS: p_carrid TYPE sflight-carrid, 
            percent  TYPE p LENGTH 1 DECIMALS 0. 

DATA sflight_tab TYPE TABLE OF sflight. 
FIELD-SYMBOLS <sflight> TYPE sflight. 

SELECT * 
       FROM sflight 
       WHERE carrid = @p_carrid AND 
             fldate = @sy-datum 
       INTO TABLE @sflight_tab. 

IF sy-subrc = 0. 
  LOOP AT sflight_tab ASSIGNING <sflight>. 
    <sflight>-price = 
      <sflight>-price * ( 1 - percent / 100 ). 
  ENDLOOP. 
ENDIF. 

UPDATE sflight FROM TABLE @sflight_tab.

Continue

UPDATE dbtab - set_expression