Update DB table from internal table constructed inline with VALUE

1
votes

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

2
abap
I understand why it doesn't work but I don't understand what you want to achieve, can you explain in words please, which variables identify what row you want to update? (it's obvious for me that it's not the values from ls_doc61) 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. - Sandra Rossi
I updated the question. i just want to update one field named ESTADO. - Ivan Diaz Salas
OK for the column you want to update but I don't understand what rows you want to update: all rows that are in it_doc61? - Sandra Rossi
Yes. i want to update all the rows from it_doc61 - Ivan Diaz Salas
In fact, this question "how to do GOAL with OBJECT because REASON" is misleading, as the goal is not clearly stated (updating one column) and both the reason (Code Inspector, performance) and object ("new syntax") are based on incorrect assumptions. People risk to propose you a solution that doesn't correspond to your actual need. Even I feel I shouldn't have answered to your ultimate goal (if I understood it well), because the question does not reflect it, and Stack Overflow requires that people answer well-asked questions (comments are not part of the question). - Sandra Rossi

2 Answers

3
votes

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 ) ) ).
0
votes

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.