ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Write Accesses → UPDATE dbtab → UPDATE dbtab - source
UPDATE dbtab - set_expression
Other versions: 7.31 | 7.40 | 7.54
Syntax
... {col = f}
| {col = col + f}
| {col = col - f}
| (expr_syntax) ...
Variants
1. ... col = f
2. ... col = col + f
3. ... col = col - f
4. ... (expr_syntax)
Effect
Change expressions for the addition SET of the statement UPDATE.
These change expressions in the comma-separated list can be combined in any way required after SET.
Variant 1
... col = f
Effect
Assigning a value. Columns of the database table or view dbtab specified
in target can be specified for col. For f, a
literal, a
host variable, a
host expression, or a column name
dbtab~comp of a different column in the database table can be used. A column can occur in a single change expression after SET only.
The statement UPDATE assigns the content of f
to the columns col in all the rows defined by the WHERE
condition. If the column has one of the data types DECFLOAT34 or DECFLOAT16, the data object has a numeric
data type. If the column has the data type UTCLONG, the data object must have the data type utclong. If the data types are not
compatible, they are converted as follows:
-
When assigning a data object that is not a reference variable for a
LOB handle, the content is converted, if required, to the
ABAP data type appropriate to the data type of the column (and in accordance with the
conversion rules). Any errors in the conversion
raise the appropriate non-handleable exception. The content of
fmust match the data type of the column in accordance with the rules for lossless assignments. This is checked by the strict modes of the syntax check from Release 7.40, SP08 and can raise an exception. -
When assigning a data object that is a reference variable for a
LOB handle, the column
colmust be a LOB with an appropriate type. If the reference has a writer stream type, a writer stream 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. -
When assigning a different column, the content is converted, if required, in the database system. Whether
a conversion is possible and the conversion rules are specific to the database system. Any errors in the conversion raise the handleable exception CX_SY_OPEN_SQL_DB.
In the assignment, the assigned value must fit in the value range of the column. Otherwise the handleable exception CX_SY_OPEN_SQL_DB is raised. This applies in particular for:
- Assignments to numeric columns
-
Assignments to columns of the type SSTRING, STRING, or RAWSTRING. The assigned content must not be longer
than the maximum length of the columns in the database structure. This length can be determined using the built-in function
dbmaxlen.
If a column is specified for f, changes to this column that are made in the same UPDATE statement are ignored during the assignment.
Notes
- When a host expression is specified on the right side, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.
-
No enumerated objects, however, can be specified for
f. -
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.
Example
The following UPDATE statement sets the specified columns to the specified values in every row of the database table.
DELETE FROM demo_update.
INSERT demo_update FROM TABLE @( VALUE #( ( id = 'X' )
( id = 'Y' )
( id = 'Z' ) ) ).
DATA(num) = 300.
UPDATE demo_update SET col1 = 100,
col2 = demo_update~col1,
col3 = @num,
col4 = @( num + 100 ).
Variant 2
... col = col + f
Effect
Addition of a value. For col
and f, the same applies as to the assignment of a value. Here, col
must have a numeric data type. col must be specified either with the prefix
dbtab~ on both sides or without it on both sides. The data types DF16_RAW and DF34_RAW and the obsolete types DF16_SCL and DF34_SCL are not allowed.
The statement UPDATE adds the value of f to the
content of the column col, in all the rows defined by the WHERE condition. Otherwise, the same rules apply as to the assignment of a value.
Example
The following UPDATE statement adds the specified values to the values in the specified columns in every row of the database table.
DELETE FROM demo_update.
INSERT 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 ) ) ).
DATA(num) = 300.
UPDATE demo_update SET col1 = col1 + 100,
col2 = col2 + demo_update~col1,
col3 = col3 + @num,
col4 = col4 + @( num + 100 ).
Variant 3
... col = col - f
Effect
Subtraction of a value. For col
and f, the same applies as to the assignment of a value. Here, col
must have a numeric data type. col must be specified either with the prefix
dbtab~ on both sides or without it on both sides. The data types DF16_RAW and DF34_RAW and the obsolete types DF16_SCL and DF34_SCL are not allowed.
The statement UPDATE subtracts the value of f
from the content of the column col, in all the rows defined by the WHERE condition. Otherwise, the same rules apply as to the assignment of a value.
Example
The following UPDATE statement subtracts the specified values from the values in the specified columns in every row of the database table.
DELETE FROM demo_update.
INSERT 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 ) ) ).
DATA(num) = 300.
UPDATE demo_update SET col1 = col1 - 100,
col2 = col2 - demo_update~col1,
col3 = col3 - @num,
col4 = col4 - @( num + 100 ).
Variant 4
... (expr_syntax)
Effect
Dynamic specification of change expressions. A change expression can be specified as a parenthesized
data object expr_syntax that, when the statement is executed, either contains
the syntax of one or more static change expressions (with the exception of host expressions) or is initial. The data object expr_syntax can be a character-like data object or a
standard table without
secondary table keys
and with a character-like data object. The syntax in expr_syntax is not case-sensitive. When an internal table is specified, the syntax can be distributed across multiple rows.
If expr_syntax is initial when the statement is
executed, the change expression is ignored. Invalid syntax raises a handleable exception from the class CX_SY_DYNAMIC_OSQL_ERROR.
Security Note
If used wrongly, dynamic programming techniques can present a serious security risk. Any dynamic content
that is passed to a program from the outside must be checked thoroughly or escaped before being used
in dynamic statements. This can be done using the system class CL_ABAP_DYN_PRG or the predefined function escape. See
SQL Injections Using Dynamic Tokens.
Note
If expr_syntax is an internal table with a
header line, the
table body is evaluated and not the header line.
Example
The following UPDATE statement changes the columns specified in a dynamic token to the specified values in every row of the database table.
DELETE FROM demo_update.
INSERT demo_update FROM TABLE @(
VALUE #( ( id = 'X' col1 = 1 col2 = 2 col3 = 3 )
( id = 'Y' col1 = 11 col2 = 12 col3 = 13 )
( id = 'Z' col1 = 21 col2 = 22 col3 = 23 ) ) ).
DATA(num) = 100.
DATA(expr_syntax) = `col1 = @num, ` &&
`col2 = col2 + demo_update~col1, ` &&
`col3 = col3 - @num`.
TRY.
UPDATE demo_update SET (expr_syntax).
CATCH cx_sy_dynamic_osql_error INTO DATA(exc).
cl_demo_output=>display( exc->get_text( ) ).
ENDTRY.