How to update one column of a Z-table from an internal table

[pvannest]

Hi,

Is it possible to update one column of a Z-table from the values found in an internal table, WITHOUT looping through the internal table and using update set values .. where...

For performance reasons, i would like to use:

update ztable from itab.

Problem with that, is that all columns are adjusted.

example:

my ztable has 3 columns & the following entries:

column1(key) column2 column3

A 1 0

B 2 0

C 3 0

my internal table contains the following

column1 column3

A X

C Z

result should be

column1(key) column2 column3

A 1 X

B 2 0

C 3 Z

Thx in advance for your help,

i will reward points

[Former Member]

are field symbols allowed

[pvannest]

yes

[Former Member]

use update <target> SET <f1> <f2> where condition...

SET will ensure u update only the relavant fields where the where condition is successful.

[pvannest]

Yes, this one i know,

but it isn't very performant to loop and to modify line by line.

Is this really the only solution?

What about field symbols?

thx

[pvannest]

sorry 'bout repeating this replys, i got servlet exceptions by posting my message...

[Former Member]

Hi peter,

1. Just use this simple syntax

2. Loop at itab.

MODIFY DBTAB from itab.

ENDLOOP.

3. Based upon the primary key,

the system will take care of everything,

regards,

amit m.

[pvannest]

Yes, this one i know,

but it isn't very performant to loop and to modify line by line.

Is this really the only solution?

What about field symbols?

thx

[pvannest]

Yes, this one i know,

but it isn't very performant to loop and to modify line by line.

Is this really the only solution?

What about field symbols?

thx

[pvannest]

Yes, this one i know,

but it isn't very performant to loop and to modify line by line.

Is this really the only solution?

What about field symbols?

thx

[hymavathi_oruganti]

DECALRE ANOTHER INTERNAL TABLE

data: it_dbtab type table of dbta.

MOVE-CORRESPONDING ITAB TO IT_DBTAB.

APPEND IT_DBTAB.

MODIFY DBTAB FROM TABLE IT_DBTAB.

if u want to use field-symbols, use like this.

FIELD-SYMBOLS: <FS> TYPE ANY.

LOOP AT ITAB ASSIGNING <FS>.

UPDATE DBTAB SER COLUMN3 = <FS>-COLUMN3.

ENDLOOP.

Message was edited by: Hymavathi Oruganti

[Former Member]

use the code below.

itab-<field> = <value>.

modify itab index <respective index value>.

update <ztable> from itab.

check it..

[former_member186741]

if you change the format of your internal table to match your ztable and read all other fields from the source table into it....

then you can use the style of:

update <tablename> from table <inernal table>.

[jayanthi_jayaraman]

Hi,

Try this.

I hope column1 is your key field.

select * from db into table itab.

Now itab contains enteries from your ztable.

Now loop the internal table which you have already.

loop at itab1 into wa1.

wa-column3 = wa1-column3.

modify itab from wa transporting column3 where colmn1 = wa1-column1.

endloop.

Eventhough you are looping the internal table,you are not updating the database every time.You are just updating another internal table.It won't affect must performance.

update ztable from table itab.

Kindly reward points by clicking the star on the left of reply,if it helps.

[pvannest]

It is not possible to make a download of my Z table into an internal table, because it contains millions of entries that need to be updated...

I appreciate your replies though...

[Former Member]

hi Peter,

as neil suggested..

i,ve tried it and it worked..

take this sample case..

structure of zemp is like this..

empid name empad

5 name1 pl5

10 name2 pl6

12 name3 pl7

where empid is the key..

data : itab like table of zemp with header line.

itab-empid = '5'.
itab-empad = 'PL1'.

append itab.

itab-empid = '12'.
itab-empad = 'PL2'.

append itab.

update z538Emp from table itab.

this one worked and the table is getting uploaded..

ps:reward points if it helps you..

thanks

regards

satesh

[former_member186741]

check out creating a view on your main table which is updateable but only contains a subset of the main table. Then you could do the update on the view directly from your internal table.

[Former Member]

HI Peter

loop at itab.

UPDATE (table)

SET field = itab-field

WHERE <keyfield> = itab-keyfield.

endloop.

performance will be more

regards

kishore

[pvannest]

And he didn't cleared your name column?

[Former Member]

Hi

you're asking for updating just one column.

maybe it's possible to group somehow the internal table first. Then just update for every single 'set_expression possiility'.

Example:

TYPE t_lt type range of <DB>.
data s_lt type t_lt.
data l_set_expression_field like <db>-<set_expression_field>.

...

sort <itab> by <set_expression_field>.

loop at <itab> into/assigning <structure>.

  if l_set_expression_field <> <structure>-<set_expression_field>.

    if <set_expression_field> ne inital.

      UPDATE <db> SET set_expression_field =   l_set_expression_field
       WHERE key_1 IN s_lt and
         key_2 in s_lt and
         ...
         key_n in s_lt.

     clear s_lt.

    endif.

    l_set_expression_field = <structure>-<set_expression_field>.

  else.

  (  add key fields to s_lt)

  endif.

  at last.
* neccesary for the last loop

    UPDATE <db> SET set_expression_field = <set_expression_field>
     WHERE key_1 IN s_lt and
         key_2 in s_lt and
         ...
         key_n in s_lt.

  endat.

endloop

Okay, I never tested this, but at last it look - let's say - creative....

Bye

Bernhard

[Former Member]

hi Peter,

my code dint clear the name column .. it worked fine

regards

satesh

[Former Member]

Satesh,

I tried your example on our 4.6C system and the name column of my database table <u>was</u> blanked out. This is what I expected would happen. If you are on a higher release than me, I wonder if there has been a change to the behavior of the UPDATE statement in releases higher than 4.6C.

My table ztempjrg is defined as follows:

MANDT	MANDT   (key field)
EMPID	INT4    (key field)
NAME	CHAR30
EMPAD	CHAR3

I manually added the following initial values to table ztempjrg:

<u>MANDT   EMPID   NAME    EMPAD</u>
050	5	NAME1	PL5
050	10	NAME2	PL6
050	12	NAME3	PL7

Here is my source code:

data : itab type table of ztempjrg with header line.

itab-empid = '5'.
itab-empad = 'PL1'.

append itab.

itab-empid = '12'.
itab-empad = 'PL2'.

append itab.

update ztempjrg from table itab.

After running this program, table ztempjrg contained the following:

<u>MANDT   EMPID   NAME    EMPAD</u>
050	5		PL1
050	10		PL2
050	12	NAME3	PL7

Notice that the NAME field has been blanked out because I did not populate the NAME field in my itab. The system used the blank NAME values from the itab rows as the new values.

I am curious to know what led us to different results. The lesson here is to be very careful when using an array update.

The best solution (IMHO) was mentioned above -- pre-populate your itab with the database values and then change the itab values you want to update. Assuming there are only a few records that need to be updated, you don't need to pre-populate all the table entries into itab. How about this:

data : itab type table of ztempjrg with header line.

itab-empid = '5'.
itab-empad = 'PL1'.

select single name from ztempjrg into itab-name
       where empid = itab-empid.
if sy-subrc = 0.
  append itab.
endif.

itab-empid = '12'.
itab-empad = 'PL2'.

select single name from ztempjrg into itab-name
       where empid = itab-empid.
if sy-subrc = 0.
  append itab.
endif.

if not itab[] is initial.
  update ztempjrg from table itab.
endif.

Now the resulting table entries are as follows:

<u>MANDT   EMPID   NAME    EMPAD</u>
050	5	NAME1	PL1
050	10	NAME2	PL2
050	12	NAME3	PL7

<b>NOTE:</b> Since you are now adding additional hits on the database (the select single stmts), you must consider whether this technique is less efficient than simply looping through your itab and updating the individual columns record-by-record. Which method you use to achieve the best performance really depends on the specific set of data you need to update.

Best Regards,

James Gaddis

[pvannest]

James,

Idd, the name column is cleared for those entries. That is what I expected. Strange that it worked for Satesh. I don't think it has something to do with the version of the system. Satesh, have you defined your database table in a particular way?

In my case, it won't be possible to use update from itab. Because using "select single" will not be very fast think to do. I will use the loop and "update set where", which was my first idea.

I have given points anyhow, for your trouble...

grtz