abapopensql

How can you modify only specific columns in a global table?


I'm selecting four fields in an inner-join select into an internal structure that I've created. Then, I want to update those specific fields in one of the tables under a different primary key. Here's some abstracted code (for simplicity):

SELECT global_table1~pk data1 data2 global_table2~pk1 FROM global_table1 
       JOIN global_table2 ON global_table1~pk = global_table2~pk
       INTO local_table.
"global_table2 contains the connection between pk and pk1

LOOP AT local_table INTO local_structure.
  UPDATE global_table1
         SET data1 = local_structure-data1 
             data2 = local_structure-data2
         WHERE pk = local_structure-pk1.
ENDLOOP.

Now, that worked! But the requirements got extended to them wanting a new row to be inserted if it doesn't exist yet. My first thoughts were a MODIFY, but I haven't found a way to code it in the same simplicity.

Approaches I thought of:

  1. Using update as it is now, then having a sy-subrc check and if it's 4, filling the contents of an internal structure with all the table's fields and inserting that.
  2. Selecting the entire table into a local table, then constructing some sort of CORRESPONDING mega function.
  3. Splitting the rows in my internal table into an update and an insert table respectively, and then doing a batch update and insert.

Out of those, 1. is definitely the easiest. But I can't help but think there must be an easier way. Performance is also a consideration here, as I'm working with a lot of data.


Solution

  • Sandra's solution (projection view) is the closest answer to what your title asks. In terms development you won't have to invest much into your own 1st and 3rd solutions.

    Internally modify tries to insert and if that fails tries to update (or maybe the other way around). By doing the same thing yourself, you are only doing 1 extra roundtrip, which is pretty much nothing in terms of performance unless you're working with massive tables.

    Depending on ABAP version it's 3-5 lines of code. If you need to account for different field names between local_table and global_table1, it might be a few more lines.

    data: ls_dbtab type global_table1. "Only on older ABAP
    ....
    update dbtab (the same way you do it now).
    if sy-subrc <> 0
       "ABAP 7.4+ manual. might need DBTAB name instead of #
       Insert into dbtab from @( value #( PK1 = local_structure-PK1 field1 = ... ) ).
       "ABAP 7.4+ corresponding. Mapping addition can be used to map mismatching names
       Insert into dbtab from @( corresponding #( local_structure ) ). 
       "On older ABAP
       MOVE-CORRESPONDING local_structure to ls_dbtab.
       insert into global_table1 from ls_dbtab.
    endif.
    

    Based on example you've provided you're effectively inserting from global_table1 back into global_table1 under different key, which would require additional table in join to identify missing rows and a case statement to catch null values.

    select (your fields),
      case when gt1_to_mod~mandt is not null then 'X' end as exists
      from (your current tables)
         left join global_table1 ON gt1_to_mod gt1_to_mod~pk = global_table2~pk1
    ...
    "Exists = 'X' --> Update
    "Exists = ' ' --> Isnert
    

    Questionable efficiency in terms of performance (shouldn't have a significant impact either way), but it's not a a huge investment to develop.