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