topic modify dbtab from table transporting in Application Development and Automation Discussions

modify dbtab from table transporting

Former Member — Mon, 30 Apr 2018 08:10:01 GMT

There seems to be no way to MODIFY a DB table from an internal table TRANSPORTING only certain fields.

It seems a pity to loop through the internal table and do thousands of MODIFY commands. Or, as show below, loop through the itab and load from the DB the data (e.g. field "hash") we want to retain.

Is there a more elegant solution?

" Load parts from external system
DATA(lt_part) = ycl_hybris_api=>part_list(
  attributes = 'galleryImages,code,creationtime,modifiedtime'
  sort       = 'code'
).
CHECK lt_part IS NOT INITIAL.

" Set all timestamps to now
DATA ls_part TYPE y00_hyb_parts.
ls_part-loaded = ycl_date=>timestamp( ).
MODIFY lt_part FROM ls_part TRANSPORTING loaded WHERE loaded IS INITIAL.

" We need to retain the old HASH value
LOOP AT lt_part ASSIGNING FIELD-SYMBOL(<ls_part>).
  SELECT SINGLE hash FROM y00_hyb_parts INTO CORRESPONDING FIELDS OF <ls_part> WHERE matnr = ls_part-matnr.
ENDLOOP.

" Cache the result
MODIFY y00_hyb_parts FROM TABLE lt_part transporting.
COMMIT WORK AND WAIT.

Re: modify dbtab from table transporting

pokrakam — Mon, 30 Apr 2018 08:33:49 GMT

UPDATE y00_hyb_parts SET loaded = whatever WHERE ...

Re: modify dbtab from table transporting

Former Member — Mon, 30 Apr 2018 09:05:06 GMT

The real question is how I update the DB Table from the internal table lt_part. There are various fields there that need to be updated FROM the external system.

The field loaded should only be set in the DB for those parts in lt_part which were currently loaded.

The field hash should not be changed in the DB table at all.

Re: modify dbtab from table transporting

retired_member — Mon, 30 Apr 2018 09:37:57 GMT

Since MODIFY can work as INSERT, it makes no sense to transport only part of the fields

Mike's answer is correct.

Re: modify dbtab from table transporting

pokrakam — Mon, 30 Apr 2018 10:20:45 GMT

The only way I know is to pull the existing data into an itab, update the needed info and then do an update. You could probably do something really fancy with UPDATE ... FROM TABLE VALUE #( ... )..., but at it's simplest:

SELECT * FROM y00_hyb_parts INTO TABLE @data(staging_table) WHERE ... .
   ... "merge updates onto staging_table
UPDATE y00_hyb_parts FROM TABLE staging_table.