Search code examples
abapopensql

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.

Solution

  • 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.