UPDATE dbtab - source
Short Reference
ABAP Syntax ... { {SET
set_expression [WHERE sql_cond ]}
| {FROM wa|{TABLE itab}} }.
ABAP_ALTERNATIVES:
1 ... SET set_expression [WHERE sql_cond]
2 ... FROM wa
3 ... FROM TABLE itab
What does it do? 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).
ABAP_ALTERNATIVE_1
... SET set_expression [WHERE
sql_cond ]
What does it do? After the SET adddition, the changes are specified
in a list of change expressions in
set_expression .
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 for 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 field must also be given a value.
Example ABAP Coding
Dynamic conversion of the content of an arbitrary column in an
arbitrary database table of a previous currency in Euro.
PARAMETERS: table TYPE c LENGTH 30,
column TYPE c LENGTH 30,
old_curr TYPE sycurr.
DATA: set_expr TYPE string,
condition TYPE string.
set_expr = column <(> <)><(> <)> ` = 'EUR'`.
condition = column <(> <)><(> <)> ` = old_curr`.
TRY.
UPDATE (table)
SET (set_expr)
WHERE (condition).
CATCH cx_sy_dynamic_osql_error.
MESSAGE `Error in update!` TYPE 'I'.
ENDTRY.
ABAP_ALTERNATIVE_2 ... FROM wa
What does it do? 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 handle s 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.
If 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 component s 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 handle s .
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 the client ID 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
.
Latest notes: The work area wa should always be declared in
relation to the database table or the view in the ABAP Dictionary. For
the derivation of LOB handle
structures , there are special additions
to 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 ABAP Coding 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
INTO wa
WHERE id = '00017777'.
wa-discount = '003'.
UPDATE scustom FROM wa.
ABAP_ALTERNATIVE_3 ... FROM TABLE itab
What does it do? 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 read 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.
Latest notes: 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 ABAP Coding
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
INTO TABLE sflight_tab
WHERE carrid = p_carrid AND
fldate = sy-datum.
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.
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved