'UPDATE only specific fields of database table from internal table

I need to update two fields called F1 and F2 within database table called ANLZ.

Table ANLA has a field called ACTIVE that states, whether the fields in ANLZ can be updated.

Key for connecting both tables is field ANLN1

Data declaration:

TYPES: BEGIN OF TY_ANLZ,
   ANLN1 TYPE ANLZ-ANLN1,
   ACTIVE TYPE ANLA-ACTIVE,
   F1 TYPE ANLZ-F1,
   F2 TYPE ANLZ-F2,
  END OF TY_ANLZ.

DATA LT_ANLZ TYPE STANDARD TABLE OF TY_ANLZ.

First, I do inner join that gives me only records that are active in ANLA and corresponding fields F1, F2 from ANLZ.

SELECT F1, F2, ANLA~ACTIVE, ANLA~ANLN1 FROM ANLZ AS ANLZ
   INNER JOIN ANLA AS ANLA
   ON ANLA~ANLN1 EQ ANLZ~ANLN1
   WHERE ANLA~ACTIVE EQ 'Y'
   INTO CORRESPONDING FIELDS OF TABLE @lt_anlz.

Now I need to access ANLZ and update it with the filtered ANLZ-F1 and ANLZ-F2 using static value(e.g. ****), how do I do that please? What is the most sufficient way?

I was thinking, but I'm not sure if the usage of loop is good with regards to performance(tens thousands of records).

LOOP AT lt_anlz ASSIGNING <fs_anlz>.
   UPDATE anlz SET F1 = <fs_anlz>-F1,
                   F2 = <fs_anlz>-F2
                   WHERE ANLN1 = <fs_anlz>-ANLN1.
ENDLOOP.
abapopensql


Solution 1:[1]

Bulk writing the table with one UPDATE FROM @itab will likely be faster than creating one single transaction for each record (assuming anln1 is the primary key):

UPDATE anlz FROM TABLE @lt_anlz.

Solution 2:[2]

From ABAP release 7.55 you can declare indicator structures along with the statement TYPES. The see relevant documentation here.

The addition INDICATORS of the UPDATE FROM clause can be used to specify set indicators for a work area or an internal table. The purpose of set indicators is to indicate columns to be changed. UPDATE FROM without indicators overwrites all fields of a row but when set indicators are used, only the indicated fields are updated. The addition can be specified only after UPDATE FROM for structured work areas wa or internal tables itab with a structured row type. The source work area or internal table must have a structure set_ind as last field with the same number of components as the DDIC database table to be updated, and each component serves as set indicator for one row. There is a static variant and a dynamic variant.

The example is as follows:

INSERT demo_update FROM TABLE @(
    VALUE #( ( id = 'A' col1 = '1' col2 = '10'   col3 = '100')
             ( id = 'B' col1 = '2' col2 = '20'   col3 = '200')
             ( id = 'C' col1 = '3' col2 = '30'   col3 = '300')
             ( id = 'D' col1 = '4' col2 = '40'   col3 = '400')
             ( id = 'E' col1 = '5' col2 = '50'   col3 = '500')
             ( id = 'F' col1 = '6' col2 = '60'   col3 = '600') ) ).

TYPES ind_wa TYPE demo_update WITH INDICATORS col_ind
             TYPE abap_bool.
DATA ind_tab TYPE TABLE OF ind_wa.

ind_tab = VALUE #(
       ( id = 'D' col4 = 4000 col_ind-col4 = abap_true )
       ( id = 'E' col4 = 5000 col_ind-col4 = abap_true )
       ( id = 'F' col4 = 6000 col_ind-col4 = abap_true ) ).

UPDATE demo_update FROM TABLE @ind_tab
                   INDICATORS SET STRUCTURE col_ind.

* See first result

UPDATE demo_update FROM TABLE @ind_tab.

* See second result

First result with indicators used

Client ID COL1 COL2 COL3 COL4
000 A 1 10 100 0
000 B 2 20 200 0
000 C 3 30 300 0
000 D 4 40 400 4000
000 E 5 50 500 5000
000 F 6 60 600 6000

Second result without indicators

Note how the values in column col1, col2 and col4 are set to the initial value (same value as in the internal table).

Client ID COL1 COL2 COL3 COL4
000 A 1 10 100 0
000 B 2 20 200 0
000 C 3 30 300 0
000 D 0 0 0 4000
000 E 0 0 0 5000
000 F 0 0 0 6000

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 peterulb

Related Questions