Skip to content

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

MODIFY dbtab - source

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


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

    ( SELECT subquery_clauses [
UNION ...] ) ...

Alternatives

1. ... @wa|@( expr )

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

3. ... ( SELECT subquery_clauses [UNION ...] ) ...

Effect

A non-table-like data object wa can be specified after FROM in the statement MODIFY and an internal table or a parenthesized subquery can be specified after TABLE. The work area and the internal table can be specified as a host variable or a host expression. On the one hand the content of the data objects or the results set of the subquery determines whether the row(s) are inserted or changed, and on the other hand, which values are inserted or used for changes.


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

... @wa|@( expr )

Effect

When a non-table-like work area that meets the requirements for use in ABAP SQL statements is specified as a host variable @wa or host expression @( expr ), a row is found in the database table or view that has the same content in the primary key as the corresponding initial part of the work area.

  • If a row like this is found, this row is overwritten in accordance with the same rules as for the statement UPDATE.
  • If a row like this is not found, a new row is inserted according to the same rules as for the statement INSERT.

If the change would produce a duplicate entry in a unique secondary index, it is not executed 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 MODIFY FROM. For database views, this produces a syntax check warning and for projection views a syntax error or runtime error.


Notes

  • The wa work area should always be declared with reference 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 also be raised in compatible work areas. 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 enhances the MODIFY statement implicitly with the addition FROM dbtab.


Example

Create or change a message in database table T100. If there is no message with the number 100 in the MYMSGCLASS message class in English, it will be created. Otherwise only the text is changed.

DATA(message_wa) = VALUE t100( 
  sprsl = 'EN' 
  arbgb = 'MYMSGCLASS' 
  msgnr = '100' 
  text =  'Some new message ...' ). 

MODIFY t100 FROM @message_wa.

Example

The same example as before, but with a host expression.

MODIFY t100 FROM @( VALUE #( 
  sprsl = 'EN' 
  arbgb = 'MYMSGCLASS' 
  msgnr =  '100' 
  text =  'Some new message ...'  ) ).

Alternative 2

... TABLE @itab|@( expr )

Effect

When an internal table is specified as a host variable @itab or host expression @( expr ), its rows are evaluated to overwrite existing rows or to insert new rows. The row type of the internal table must meet the prerequisites for use in ABAP SQL statements.

This behavior is platform-dependent and uses one of the following patterns (depending on the database system):

  • UPDATE followed by INSERT:
  • First, the same function as for UPDATE FROM TABLE is executed for all rows of the internal table.
Depending on the database system, the processing of the rows in the internal table can also be split into blocks here.
  • Row by row MODIFY
In a loop, each row in the internal table is edited in accordance with the rule above for MODIFY ... FROM wa. Here, the database system specifies whether processing continues in cases where a row cannot be inserted due to a unique secondary index.

These two platform-dependent processing methods can produce different results in cases where the database table has unique secondary indexes (see executable example).

If the change to a row in the internal table would produce a duplicate entry in a unique secondary index, the corresponding row is not inserted and sy-subrc is set to 4. If the internal table is empty, no rows are processed. However sy-subrc is still set to 0. The system field sy-dbcnt is set to the number of rows that are actually processed.


Notes

  • To prevent platform-dependent behavior, MODIFY ... FROM itab should only be applied to database tables without unique secondary indexes. If not, the required behavior must be programmed explicitly using UPDATE and INSERT or using LOOP AT itab and MODIFY.
  • 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.
  • When an internal table is specified, locators can be used as a source, but no writer streams can be created.


Example

Create or change multiple rows in database table DEMO_UPDATE. This database table does not have a unique secondary index and the result is therefore not platform-specific.

TYPES itab TYPE TABLE OF demo_update WITH EMPTY KEY. 

DATA(itab) = VALUE itab( 
  ( id = 'X' col1 =  1 col2 =  2 col3 =  3 col4 =  4 ) 
  ( id = 'Y' col1 = 11 col2 = 12 col3 = 13 col4 = 14 ) 
  ( id = 'Z' col1 = 21 col2 = 22 col3 = 23 col4 = 24 ) ). 

MODIFY demo_update FROM TABLE @itab.

Example

The same example as before, but with a host expression.

MODIFY demo_update FROM TABLE @( 
  VALUE #( ( id = 'X' col1 =  1 col2 =  2 col3 =  3 col4 =  4 ) 
           ( id = 'Y' col1 = 11 col2 = 12 col3 = 13 col4 = 14 ) 
           ( id = 'Z' col1 = 21 col2 = 22 col3 = 23 col4 = 24 ) ) ).

Executable Example

MODIFY FROM TABLE

Alternative 3

... ( SELECT subquery_clauses [UNION ...] ) ...

Effect

If a parenthesized subquery is specified, the rows in the results set defined by subquery_clauses are evaluated to overwrite the existing rows or to insert new rows. The language element UNION can be used to combine the results sets of multiple subqueries. In this case, special rules query_clauses apply for specifying clauses.

With a subquery, the statement MODIFY demonstrates platform-dependent behavior (as when an internal table is specified) in accordance with one of two patterns:

  • UPDATE followed by INSERT
  • Row by row MODIFY

With respect to the following actions,

  • client handling,
  • inserting or modifying the data in the database table or view , and
  • handling of null values,

the same rules and notes apply as when using a subquery as a data source of the statement INSERT.

If the results set of the subquery is empty, no rows are processed and sy-subrc is set to 4.

A subquery in the statement MODIFY is not supported by all databases. This means a syntax check warning from the extended program check can occur that can be hidden using the pragma ##db_feature_mode[modify_from_select]. If this is detected at runtime on a database that does not support the pragma, a handleable exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised.


Notes

  • The constant MODIFY_FROM_SELECT of the class CL_ABAP_DBFEATURES can be used to query whether the current database supports subqueries in the statement MODIFY.
  • When a subquery is used in the statement MODIFY, the syntax check is performed in strict mode from Release 7.53, which handles the statement more strictly than the regular syntax check.


Example

A subquery of the statement MODIFY reads all rows in the database table demo_join3 and uses the results set to modify the database table demo_join1. Two rows of the results set overwrite existing rows in demo_join1 and a row is inserted.

DELETE FROM demo_join1. 
INSERT demo_join1 
  FROM TABLE @( VALUE #( ( a = 'a1' b = 'b1' c = 'c1' d = 'd1' ) 
                         ( a = 'a2' b = 'b2' c = 'c2' d = 'd2' ) ) ). 
DELETE FROM demo_join3. 
INSERT demo_join3 
  FROM TABLE @( VALUE #( ( i = 'a1' j = 'j1' k = 'k1' l = 'l1' ) 
                         ( i = 'a2' j = 'j2' k = 'k2' l = 'l2' ) 
                         ( i = 'i3' j = 'j3' k = 'k3' l = 'l3' ) ) ). 

MODIFY demo_join1 FROM ( SELECT i, j, k, l FROM demo_join3 ). 

SELECT * 
       FROM demo_join1 
       INTO TABLE @DATA(result). 

cl_demo_output=>display( result ).