Updating a DB table using AMDP or SQL script

[Mkatta]

Hello All,

I have a seldom problem. Just to give some back ground.

1. We have a Z DB table with 126 fields(Yes exactly 126) having 3 key fields which has like 250+ million entries.

2. I need to update four fields in the Z table(Step1) from a CDS view which has 7 fields( 3 key fields of the ZDB table + four fields which need to be updated) .

Here is the problem.

Here is my code approach.

a. lt_tab = data from the CDS_VIEW.(which will fetch like 20 million entries)

I'm not sure how to approach the updating part. I cannot use MODIFY as I have to get the entire table data(which is huge performance).

Just for Info we are using HANA DB.

Any recommendation and Suggestions are highly appreciated.

[horst_keller · Accepted solution]

Either you fetch the data into an internal table and then use UPDATE SET of Open SQL or you stay on the DB and update directly there in AMDP without loading to ABAP. The latter should be better in performance. But there is no use in first loading the data to ABAP and then pushing them back to AMDP.

[Mkatta · Accepted solution]

Hello Horst,

I appreciate the quick turnaround. Yes I completely agree with you. We have two options

1. UPDATE set by looping through lt_tab which has like 20 million entries.( which literally kills the performance)

2. If I use the AMDP, I am not sure because the lt_tab has 7 columns( 3 key fields of the DB table) and actual DB table has 126 columns. Can you please guide me how to go with this approach.

Thanks,

Murthy

[horst_keller · Accepted solution]

This does not belong to the realms of ABAP. You have to learn SQLScript for that. And by the way, you don't use the internal table there but access the DB view of the CDS view directly.

[Sijin_Chandran · Accepted solution]

Hello Horst,

As mentioned by you,

Either you fetch the data into an internal table and then use UPDATE SET of Open SQL 

Can you please elaborate on how to use Internal Table with UPDATE SET.

As I could see in the documentation that, UPDATE SET works only if all the records has a common WHERE clause value for a column:

[former_member633271 · Accepted solution]

May I know the syntax on how to update the DB table from internal table in AMDP.

I tried below syntax but getting syntax error.

udpate DB_TABLE as a from :INTERNAL_TABLE as b

set a.field1 = b.field1

where a.field1 = b.field1;