SAP NetWeaver AS ABAP Release 750, ©Copyright 2016 SAP AG. All rights reserved.
ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Write Accesses → UPDATE dbtab →
UPDATE dbtab - source
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, which is not table-like or an itab internal table, can be specified after FROM 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). 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.
... 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 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
Example
See UPDATE, Use of SET.
... FROM wa
Effect
If a work area wa is specified (as a host variable or host expression), which is not table-like, 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 Open SQL statements.
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.
By default, an automatic client handling is performed, which means that any client identifier specified in wa is ignored and 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
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' ) ).
... FROM TABLE itab
Effect
If an internal table itab is specified as a host variable or host expression, 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 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 produce a duplicate 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.
Notes
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 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.
Example
The same example as before but with an inline declaration and a host expression.
PARAMETERS: p_carrid TYPE sflight-carrid,
percent TYPE p LENGTH 1 DECIMALS 0.
SELECT *
FROM sflight
WHERE carrid = @p_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.