CODEWITHSUNDEEP
abap
Ivan Diaz Salas
Ivan Diaz Salas

Reputation: 319

Update DB table from internal table constructed inline with VALUE

I am trying to Update a Database Table with several rows at the same time. I just need to update the field named ESTADO from an internal table.

I dont want to do that inside of a loop statement. this is because of code inspector tool and performance.

I tried to find some information about the new abap syntax and i found an inline statement to avoid loop.

UPDATE ZBWEVATDOC61 FROM TABLE @( VALUE #(
        FOR ls_doc61 IN it_doc61 WHERE ( cuv = ls_doc61-cuv And  folio =  l 
                   s_doc61-folio and folio_interno = ls_doc61-folio_interno )
          ( VALUE #(
              BASE ls_doc61
              estado = ls_doc61-estado  )  )  ) ) .
    IF sy-subrc eq 0.
      commit work AND WAIT.
    ENDIF.

I tried to use the WHERE statement to specify the row to update, but is not working

Upvotes: 1

Views: 11383

Answers (2)

Suncatcher
Suncatcher

Reputation: 10621

The following syntax works:

TYPES: ttcurr TYPE TABLE OF tcurr WITH EMPTY KEY.

SELECT ukurs, tcurr, gdatu
  FROM tcurr
  INTO TABLE @DATA(ltcurr)
  UP TO 100 ROWS.

DATA(it_modified) = VALUE ttcurr( FOR ls_tcurr IN ltcurr ( ukurs = ls_tcurr-ukurs / 1000 tcurr = ls_tcurr-tcurr gdatu = ls_tcurr-gdatu ) ).

UPDATE tcurr FROM TABLE @(
    VALUE ttcurr(
        FOR ls_curr IN it_modified WHERE ( tcurr NE 'EUR' AND gdatu > '79989898' )
          ( ukurs = ls_curr-ukurs ) ) ).

BASE is used wrongly in your snippet, it is utilized when you fill some itab with table expression and want to preserve its previous contents, it accepts only itab operands. In our case, when updating dbtab it can be omitted.

You cannot use inline types in this statement, also you cannot use auto-generated var ls_doc61 for comparison in WHERE.

Try to change your snippet to something like that:

TYPES: ttdoc TYPE TABLE OF ZBWEVATDOC61 WITH EMPTY KEY.
UPDATE ZBWEVATDOC61 FROM TABLE @(
VALUE ttdoc(
    FOR ls_doc61 IN it_doc61 WHERE ( cuv = 'smth' AND  folio = 'smth' AND folio_interno = 'smth' )
      ( estado = ls_doc61-estado ) ) ).

Upvotes: 3

Sandra Rossi
Sandra Rossi

Reputation: 13629

The statement UPDATE dbtab FROM TABLE itab, whatever itab is an internal table being either a data object or resulting from a constructor expression ("new syntax"), requires that itab has lines of the same structure than dbtab, it means that all columns of dbtab will be updated, and this statement has no other better option.

1) The only solution to mass update given columns of given rows is this way:

  • Create a "database view" on the table you want to update, by selecting only the columns concerned + the columns needed to select the rows that you define as key fields (checkbox at the right of the column name), and choose the access "read and change" so that UPDATE can be used (at least).
  • In your program, define an internal table with lines typed like the database view.
  • Use UPDATE dbtab FROM TABLE itab to update the database view. The rows defined in the key fields will be selected, and the non-key columns will be updated.
  • I don't talk here about how to write a constructor expression (... @( VALUE #( ... ) )), because your assumption that it could solve your issue was wrong.

There are some ways in ABAP to either update a few columns or several rows but not both:

  • 2) Set given columns to fixed values - in all updated row(s) those columns will have the same values: UPDATE dbtab SET col1 = value1 col2 = value2 ... \[WHERE ...\]. You may repeat UPDATE ... SET ... inside a loop so that to mimic a mass update. It will be slower than updating via a database view (case 2), and I am not sure if it's faster or slower than case 3 (may depend on number of columns).
  • 3) You may still consider the use of UPDATE dbtab FROM TABLE itab if you can be sure that itab contains correct values in all other columns that you're not interested in. You may consider preventing concurrent updates (by other programs running in parallel or eventually the same program run by another user) by using locks, to avoid that some updates were done between the SELECT to initialize the internal table and the UPDATE.

NB: I don't understand why you say that LOOP is a problem for Code Inspector and for performance. The constructor expressions ("new syntax" as you say) are used to avoid intermediate variables, for better readability and to focus on final goal.

Upvotes: 0

Related Questions