Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Write Accesses →  UPDATE dbtab 

UPDATE dbtab - source

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


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

    | {FROM {@wa|@( expr )}|{TABLE @itab|@( expr )}} } ...

Alternatives

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

2. ... FROM @wa|@( expr )

3. ... FROM TABLE @itab|@( expr )

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.

After FROM a non-table-like data object, or after TABLE an internal table, can be specified as a host variable or host expression. The content of these objects determines which rows are changed and which values are used to overwrite the row(s).


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] [db_hints]

Extras

1. ... WHERE sql_cond
2. ... db_hints

Effect

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

The content of primary key fields can be modified only if the affected database table is not associated with a search help. If these changes would create a row which would produce duplicate entries in the primary key or a unique secondary index of the database table, 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, blanks 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.

Executable Example

UPDATE, Use of SET

Addition 1

... WHERE sql_cond

Effect

The addition WHERE determines in which rows of the database table the changes are made. Those rows are change for which the logical expression sql_cond is true. The relational expressions of the logical expression sql_cond are subject to the restriction that no subqueries can be evaluated on the modified database table. If no WHERE condition is specified, all the rows in the target of the statement are modified by default. In a client-specific target, these are the rows of the current client. Implicit client handling can be defined using the addition USING. The client column of a client-specific target cannot be specified in the WHERE condition.


Notes

  • If the data source is accessed using generic table buffering, the buffered area must be specified in full in the WHERE condition. If not, buffering is bypassed.
  • If the data sources are accessed using single record buffering, the conditions joined using AND in the WHERE condition must be specified for all key fields of the primary key. If not, buffering is bypassed.
  • If implicit client handling is disabled using the addition CLIENT SPECIFIED (obsolete here), it is still possible to use a client column as an operand in the WHERE condition.


Example

Sets multiple columns in a row determined using a WHERE condition.

DELETE FROM demo_update. 
INSERT demo_update FROM @( VALUE #( id = 'X' ) ). 

UPDATE demo_update SET col1 = 100 
                       col2 = 200 
                       col3 = 300 
                       col4 = 400 WHERE id = 'X'.

Addition 2

... db_hints

Effect

In this variant, database hints can be specified using db_hints.

Alternative 2

... FROM @wa|@( expr )

Effect

If a non-table-like work area is specified as a host variable @wa or host expression @( expr ), a row is found in the database table whose primary key content is the same as that of the corresponding initial part of the work area. The work area must meet the prerequisites for use in ABAP 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 read 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 produce a duplicate entry in a unique secondary index, the row is not changed and sy-subrc is set to 4.

Implicit ABAP SQL client handling applies, in which a client ID specified in wa is ignored. The current client is used by default. The addition USING CLIENT can be used to switch to an explicitly specified client. The addition CLIENT SPECIFIED can be used to switch to the client specified in wa.

Views whose fields are all key fields should not be written to using UPDATE FROM. For database views, this produces a syntax check warning and for projection views a syntax error or runtime error.


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.
  • Exceptions due to invalid values can be raised in compatible work areas too. For example, components of the types d and t can contain invalid dates and times that are not accepted by columns of the types DATN and TIMN.
  • If a constructor expression is specified as a host expression for the work area wa, for which the data type is inferred using the # character, a structure consisting of all the database table columns is created as the type.
  • 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.

Example

The same example as before but with an inline declaration and a host expression.

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

UPDATE scustom FROM @( VALUE #( BASE wa discount = '003' ) ).

Alternative 3

... FROM TABLE @itab|@( expr )

Effect

If an internal table is specified as a host variable @itab or host expression @( expr ), 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 meet the prerequisites for use in ABAP 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 produce a duplicate entry in a unique secondary index, the row is not changed and sy-subrc is set to 4. Then processing continues with the next row.

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.


Notes

  • When an internal table is used, package by package processing performs a read (running in parallel to an UPDATE on the same rows) that partially shows the new status and partially shows the old status.
  • Statement UPDATE FROM TABLE changes all rows for which this is possible. If sy-subrc contains the value 4 after the statement has been executed, this does not mean that no rows were changed. It simply means that not all of the rows in the internal table could be taken into account.
  • If a constructor expression as a host expression is specified as an internal table itab, for which the data type is inferred using the # character, a structured standard table (whose row type consists of all the database table columns) with an empty table key is created as the type.


Example

Reduces the flight price for all of today's flights of an airline carrier in the database table SFLIGHT by the percentage percent. The new price is always performed in an internal table sflight_tab (declared inline) and the database table is changed accordingly.

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

cl_demo_input=>new( 
  )->add_field( CHANGING field = carrid 
  )->add_field( CHANGING field = percent )->request( ). 

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

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

UPDATE sflight FROM TABLE @sflight_tab.

Example

The same example as above, but the new price is calculated in a host expression for the internal table.

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

cl_demo_input=>new( 
  )->add_field( CHANGING field = carrid 
  )->add_field( CHANGING field = percent )->request( ). 

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

IF sy-subrc = 0. 
  UPDATE sflight FROM TABLE @( VALUE #( 
    FOR <sflight> IN sflight_tab 
      ( VALUE #( 
          BASE <sflight> 
          price = <sflight>-price * ( 1 - percent / 100 ) ) ) ) ). 
ENDIF.

Continue

UPDATE dbtab - set_expression