Question Re: UPDATE temporary table in AMDP Class Method in Technology Q&A

UPDATE temporary table in AMDP Class Method

Former Member — Wed, 25 Oct 2017 16:41:15 GMT

Dear experts,

I need to change only one field in a dataset and upsert it to a physical db table with AMDP. This physical table has too many columns which I do not want to specify all fields, and also developers can add some fields in future and when a new field added I do not want to edit code again and again.

Please see ZAG_SIM is a physical table and lt_result temp table type of ZAG_SIM should read base version and copy this data changing version column entries.

Please advice why UPDATE command is not accepted in eclipse editor?

Thanks in advance,

Ergin Ozturk

Re: UPDATE temporary table in AMDP Class Method

pfefferf — Thu, 26 Oct 2017 15:11:21 GMT

Updates on a table variable are not supported by the keyword UPDATE. Since HANA 2.0 SPS01 you can do updates with another syntax. Details can be found here. But personally I haven't tried it yet within an AMDP (maybe there are further restrictions).

If you are not on a HANA revision level which supports that, you can use an index based cell access to update your column (of course a loop has to be done over all entries and each data record has to be accessed by its index).

Another option would be to use a local temporary row table like that:

do
begin
  create local temporary row table #test like "ZAG_SIM";
  insert into #test select * from "ZAG_SIM";
  update #test set "VERSION" = :lv_temp;
  ...
  drop table #test;
end;

You can try both options if you like and share your results on the performance.

Regards,
Florian

Re: UPDATE temporary table in AMDP Class Method

Former Member — Fri, 27 Oct 2017 06:49:55 GMT

Thanks Florian for the effort but AMDP has other limitations, and in documents AMDP is overlooked all the time.

Please see log in the screenshot, local table creation is not supported.

Re: UPDATE temporary table in AMDP Class Method

pfefferf — Fri, 27 Oct 2017 07:30:26 GMT

Too bad. Have you tried the index-based cell access too?

Re: UPDATE temporary table in AMDP Class Method

Former Member — Mon, 30 Oct 2017 14:00:15 GMT

Thank you Florian,

FOR loop with index access did the job.

FOR i IN 1 .. :lv_counter DO

lt_result.VERSION[:i] = :lv_temp ;

END FOR;

Re: UPDATE temporary table in AMDP Class Method

rahulpippalla — Wed, 07 Apr 2021 15:12:25 GMT

Hello erginozturk,

It might be an late answer �� ,

But this may help to other.

The easiest way for this is by using select itself.

we can do it as below for the above code:

lt_result = select feild1 , feild2, :lv_temp as version where version = :lv_base and mandt = session_context( 'CLIENT' );

We don't have to loop which may have performance to it.

Regards,

Rahul.