UPDATE is a keyword used in SAP ABAP programming.This tutorial covers its introduction & syntax details.
UPDATE
Variants
1. UPDATE dbtab SET s1 … sn.
2. UPDATE dbtab. or
UPDATE *dbtab. or
UPDATE (dbtabname) … .
3. UPDATE dbtab FROM TABLE itab. or
UPDATE (dbtabname) FROM TABLE itab.
Effect
Updates
values in a database table (see Relational database ). You can specify
the name of the database table either directly in the form dbtab or at
runtime as contents of the field dbtabname . In both cases, the table
must be known to the ABAP/4 Dictionary . If you specify the name of the
database table directly, the program must also contain an appropriate
TABLES statement. Normally, lines are updated only in the current
client. Data can only be updated using a view if the view refers to a
single table and was created in the ABAP/4 Dictionary with the
maintenance status “No restriction”.
UPDATE belongs to the Open SQL command set.
Notes
Authorization checks are not supported by the UPDATE statement. You must include these in the program yourself.
Changes
to lines made with the UPDATE command only become final after a
database commit (see LUW ). Prior to this, any database update can be
canceled by a database rollback (see Programming transactions ).
In
the dialog system, you cannot rely on the database system locking
mechanism alone to synchronize simultaneous access to the same database
by several users. Therefore, it is often necessary to use the SAP
locking mechanism .
Variant 1
UPDATE dbtab SET s1 … sn.
Additions
1. … WHERE condition
2. … CLIENT SPECIFIED
Effect
Updates
values in a database table. If there is no WHERE clause , all lines (in
the current client) are updated. If a WHERE condition is specified,
only those records which satisfy the condition are updated.
The
SET clause s1 … sn identifies the columns to be updated and assigns
values to them. Three types of SET statements si are supported:
f = g In all selected lines, the database table column determined by f receives the values of the ABAP/4 field or literal g .
f
= f + g In all selected lines, the contents of the ABAP/4 field or
literal g is added to the value in the database table column determined
by f . The NULL value remains unchanged. This statement can only be
applied to a numeric field.
f = f – g In all selected lines, the
contents of the ABAP/4 field or literal g is subtracted from the value
in the database table column determined by f . The NULL value remains
unchanged. This statement can only be applied to a numeric field.
When the command has been executed, the system field SY-DBCNT contains the number of updated lines.
The return code value is set as follows:
SY-SUBRC = 0 At least one line was updated,
SY_SUBRC = 4 No line was updated because no line could be selected.
Note
With pooled and cluster tables, an UPDATE cannot change any primary key field.
Examples
Update discount for all customers (in the current client) to 3 percent:
TABLES SCUSTOM.
UPDATE SCUSTOM SET DISCOUNT = ‘003’.
Note
The ‘colon and comma’ logic in the program fragment
UPDATE SCUSTOM SET: DISCOUNT = ‘003’,
TELEPHONE = ‘0621/444444’
WHERE ID = ‘00017777’.
defines record chains,
not
through a single statement which updates the discount and the telephone
number of the customer with the customer number ‘00017777’,
but
by means of two statements where the first updates the discount for all
customers and the second changes the telephone number of the customer
with the customer number ‘00017777’.
Addition 1
… WHERE condition
Effect
Updates only those lines which satisfy the WHERE clause condition .
Example
Increase the number of occupied seats on Lufthansa flight 0400 on 28.02.1995 by 3 (in the current client):
TABLES SFLIGHT.
UPDATE SFLIGHT SET SEATSOCC = SEATSOCC + 3
WHERE CARRID = ‘LH’ AND
CONNID = ‘0400’ AND
FLDATE = ‘19950228’.
Addition 2
… CLIENT SPECIFIED
Effect
Switches
off automatic client handling. This allows you to update across all
clients when using client-specific tables. The client field is treated
like a normal table field, for which you can formulate suitable
conditions in the WHERE clause.
This addition must immediately follow the database table name.
Example
Increase the number of occupied seats on Lufthansa flight 0400 on 28.02.1995 by 3 in client 2:
TABLES SFLIGHT.
UPDATE SFLIGHT CLIENT SPECIFIED
SET SEATSOCC = SEATSOCC + 3
WHERE MANDT = ‘002’ AND
WHERE CARRID = ‘LH’ AND
CONNID = ‘0400’ AND
FLDATE = ‘19950228’.
Variant 2
UPDATE dbtab. or
UPDATE *dbtab. or
UPDATE (dbtabname) … .
Additions
1. … FROM wa
2. … CLIENT SPECIFIED
Effect
These
are SAP-specific short forms which update one single line of a database
table. The primary key for identifying the line to be updated and the
values to be changed when specifying the database table name in the
program are taken from the table work area – dbtab or *dbtab . If the
database table name is determined at runtime, you need to use the
addition … FROM wa .
When the command has been executed, the system field SY-DBCNT contains the number of updated lines (0 or 1).
The return code value is set as follows:
SY-SUBRC = 0 The specified line was updated,
SY_SUBRC = 4 No line was updated because no line with the specified primary key exists.
Examples
Update discount for the customer with the customer number ‘00017777’ to 3 percent (in the current client):
TABLES SCUSTOM.
SCUSTOM-ID = ‘00017777’.
SCUSTOM-DISCOUNT = ‘003’.
UPDATE SCUSTOM.
Addition 1
… FROM wa
Effect
Takes
the values for the line to be updated not from the table work area
dbtab , but from the explicitly specified work area wa . Here, the data
is taken from wa , moving from left to right according to the structure
of the table work area dbtab (defined with TABLES ). Since the
structure of wa is ignored, the work area wa must be at least as wide
(see DATA ) as the table work area dbtab and the alignment of the work
area wa must correspond to the alignment of the table work area.
Otherwise, a runtime error occurs
Example
Update the telephone number of the customer with the customer number ‘12400177’ in the current client:
TABLES SCUSTOM.
DATA WA LIKE SCUSTOM.
WA-ID = ‘12400177’.
WA-TELEPHONE = ‘06201/44889’.
UPDATE SCUSTOM FROM WA.
Note
If
you do not explicitly specify a work area, the values for the line to
be updated are taken from the table work area dbtab , even if the
statement appears in a FORM or FUNCTION where the table work area is
held in a formal parameter or a local variable.
Addition 2
… CLIENT SPECIFIED
Effect
Like variant 1.
Variant 3
UPDATE dbtab FROM TABLE itab. or
UPDATE (dbtabname) FROM TABLE itab.
Addition
… CLIENT SPECIFIED
Effect
Mass
update of several lines in a database table. Here, the primary key for
identifying the lines to be updated and the values to be changed are
taken from the lines of the internal table itab . The lines of the
internal table must satisfy the same conditions as the work area wa in
addition 1 to variant 2.
The system field SY-DBCNT contains the
number of updated lines, i.e. the number of lines in the internal table
itab which have key values corresponding to lines in the database table.
The return code value is set as follows:
SY-SUBRC = 0 All lines from itab could be used to update the database table.
SY_SUBRC
= 4 At least one line of the internal table itab in the database table,
had no line with the same primary key. The other lines of the database
table were updated.
Note
If the internal table itab is empty, SY-SUBRC and SY-DBCNT are set to 0.
Addition
… CLIENT SPECIFIED
Effect
Like variant 1.