topic Re: Modify database table in Application Development and Automation Discussions

Modify database table

Former Member — Tue, 19 Jul 2005 03:03:32 GMT

Hi All,

i have a database table with 10 fields,i need just 5 of these 10 fields in an internal table and do the processing based on this and later modify only these 5 fields in the database table based on values in internal table.

something like, modify dbtab from itab

modify with transporting option doesn't work for database tables

is there any other way to do this .

Re: Modify database table

Former Member — Tue, 19 Jul 2005 03:09:39 GMT

Why can't you have the internal table with the 10 fields and not do any operation on those fields which you don't want. That way no value will be entered into those fields on DB.

Ravi

Re: Modify database table

Former Member — Tue, 19 Jul 2005 03:31:10 GMT

Hi Ravi,

right now that's the way i have done.

it's for performance reasons that i'm trying for other options,becaus there's no point in selecting fields if u r not making use of it

Re: Modify database table

jayanthi_jayaraman — Tue, 19 Jul 2005 03:39:19 GMT

Hi,

You can do that by using update or modify statement.

Ex:

data : itab type standard table of db,

wa type db.

select * from db into table itab.

loop at itab into wa.

*Manipulating the fields

wa-field1 = wa-field1 * 2.

*Modifying the internal table

modify itab from wa index sy-tabix transporting field1.

endloop.

*Modifying the database table from the modified internal table

update db from table itab.

If it is useful,reward points.

If you need more clarifications,get back with the problem you are facing.

Re: Modify database table

Former Member — Tue, 19 Jul 2005 03:42:23 GMT

Hi Raghavendra,

Even if you are not modifying the other fields, you need to select them into your internal table. Otherwise you will not be able to update the database table properly.

If your internal table has got only 5 fields, then you cannot uniquely identify which row of the database table corresponds to which row in the internal table (unless these 5 fields include all the primary key fields).

So, if your database table has got 10 fields of which 1 and 2 are key fields, in the worst case, you will have to select 5 + 2 = 7 fields. I suppose your internal table will have 10 fields in it as well, so as to be able to update the database table. Now, the remaining 3 fields will be empty. So when you do an update or a modify, the values for these three fields will be overwritten by the blank values (coming from the interal table). This may not be desirable.

There's one variant of the UPDATE statement which allows you to specify which fields have to be updated. but that will work only for a single record. Which means that for multiple records, you need to put that UPDATE statement in the LOOP. Which is certainly not a good idea.

So, in your case, you need to select all the values from the database even if you do not intend to modify some of them. even if this approach requires more memory to run, it is okay. All other approaches will take up too much of time.

Hope that helps. If you have further doubts, please get back. Otherwise please reward points and close the thread.

Regards,

Anand Mandalika.

Re: Modify database table

Former Member — Tue, 19 Jul 2005 03:45:35 GMT

Hi jayanthi,

this is exactly the way i have done it right now,

functionality wise no problem it works fine,

the problem is goin to be in performance,

suppose i have 1 lakh records ,then selecting 5 extra fields for 1 lakh records would certainly have an effect on performance.that's the reason i'm in search of something where we can select only 5 fields from the db and later modify only those 5 fields in the db table.

for eg to modify only one field of an internal table which has 5 fields we can do like this

modify itab transporting fieldname.

is there any way to do the same on db table

Re: Modify database table

Former Member — Tue, 19 Jul 2005 03:53:05 GMT

Hi Raghavendra,

I have observed that you have not rewarded any points to any of the questions that you have asked earlier. Please spend a few seconds re-visiting those posts of yours. Reward points as you think appropriate for the responses that you got, if they have helped you. And if your problem is solved, then please close the thread by either -

1. Rewarding 10 points to the answer that solved your problem

2. choosing the option Solved it on my own for your post.

Anticipating a quick response.

Thanks and Regards,

Anand Mandalika, Moderator @ SDN (ABAP Programming).

Re: Modify database table

jayanthi_jayaraman — Tue, 19 Jul 2005 03:55:04 GMT

Hi,

Check this statement suits your requirement.In this you can update set of records in database table which matches the where condition.

Update db set field1 = value1

field2 = value2

where field3 = value3.

But if you want to use internal table to modify the database the table,both the internal table and database table needs to be of same structure.For that you need to do something as ZI told you before.

Hope this answers your question.

Re: Modify database table

Former Member — Tue, 19 Jul 2005 04:06:32 GMT

Hi Jayanthi,

I was just wondering what additional information your answer has got that is not already contained in mine.

Regards,

Anand Mandalika.

Re: Modify database table

jayanthi_jayaraman — Tue, 19 Jul 2005 04:11:48 GMT

Hi Anand,

You mentioned that

There's one variant of the UPDATE statement which allows you to specify which fields have to be updated. but that will work only for a single record. Which means that for multiple records, you need to put that UPDATE statement in the LOOP. Which is certainly not a good idea.

Actually what I meant in my reply is,

Update will modify set of records based on the single set of condition.

That is multiple records can be modified based on the single set of condition.