Open SQL Update

UPDATE statement used to update the data that is already existed in the table. UPDATE statement can update one or more rows at a time.

Table update can perform by using the table work area variable. The work area structure should exactly equal to the table structure. Table updation can be done in three ways and those are –

• Update single row using table work area
• Update using where condition
• Updating multiple rows using internal table

Update single row using table work area –

To update a single row in a database table with the work area data, use the following syntax –

UPDATE db-table FROM twa.

db-table is the name of a ABAP Dictionary database table and twa is the table work area.

The work area twa data overwrite the row in the database table db-table that has the same key.
The work area twa structure should exactly equal to the table structure.
The data is placed in the database table according to the table row structure, and regardless of the the work area structure.
It is a good idea to define the work area with reference to the database table structure.

If the database table contains a row with the same key as specified in the work area, the operation is completed successfully and SY-SUBRC is set to 0.
Otherwise, the line is not inserted and SY-SUBRC is set to 4.

Update using where condition –

To update the specific columns in the database table, use the following syntax –

UPDATE <db-table> 
SET column-1=value-1, column-2= value-2, ..., column-n=value-n 
[WHERE <condition>].

The WHERE clause specifies the rows that satisfies the condition. If we do not specify a WHERE clause, all lines are updated. The expressions column-1=value-1, column-2= value-2, …, column-n=value-n are different SET statements that specifies the columns(column-1, column-2, …, column-n) with the values (value-1, value-2, …, value-n) that should update with.

If at least one row is changed, the system sets SY-SUBRC to 0, otherwise to 4. SY-DBCNT contains the number of rows changed.

If we use SET statements, we cannot specify the database table dynamically.

Updating multiple rows using internal table –

To change several rows in a database table with the contents of an internal table, use the following syntax –

UPDATE db-table FROM TABLE internal-table [ACCEPTING DUPLICATE KEYS].

where db-table is the name of a target database table and internal-table is the name of the source internal table.

The internal table internal-table changes the rows in the database table db-table that have the same keys.

If the system cannot change a row because no line with the specified key exists, it does not terminate the entire operation, but continues processing the next row of the internal table.

If all rows from the internal table have been processed, SY-SUBRC is set to 0. Otherwise, it is set to 4.
If all rows are not used for updation, we can calculate the number of unused rows by subtracting the number of processed lines in SY-DBCNT from the total number of lines in the internal table. If the internal table is empty, SY-SUBRC and SY-DBCNT are set to 0.

Whenever we want to change more than one row in a database table, it is more efficient way to work than changing the row one by one.

*&---------------------------------------------------------------------*
*& Report  Z_OPENSQL
*&---------------------------------------------------------------------*
*& Written by TutorialsCampus
*&---------------------------------------------------------------------*

REPORT  Z_OPENSQL.

* Specifying table name
TABLES ZTC_TPRODUCT.
DATA: WA LIKE ZTC_TPRODUCT.

* Retrieving data from the table with product ID
SELECT *
  INTO WA
  FROM ZTC_TPRODUCT WHERE PRODUCTID = 'IFB3'.
ENDSELECT.

*Assigning new amount for the IFB3 product
WA-PRODUCT_PRICE   = 50000.

*Updating table with Work area.
UPDATE ZTC_TPRODUCT FROM WA.

*Error handling
IF SY-SUBRC  0.
  WRITE: 'Data not updated, Return code is: ', SY-SUBRC.
ELSE.
  WRITE 'Data updated successfully..'.
ENDIF.